#1
|
|||
|
|||
How to look up data from one of two workbooks
I have been using Excel 2016 to record sales and purchases of stock for my business. I used one workbook to record the transactions and other workbooks as 'Stock Records', different ones for different categories. I created a Transaction Number in the transaction sheet along with the details of how many items were bought or sold by who, when. Each item had a Bin Reference that automatically came up when the Item was picked from a drop down list. I then copy the Transaction Number into the appropriate column on that Bin Ref Stock Record and the transaction details would fill in and the running total of stock would be amended.
It works fine but I now want to streamline the system and I want to seperate Sales from Purchases. This means that when a transaction Number is entered in the Stock Record it needs to differentiate between Sales and Purchases and find the transaction information in one of two different workbooks. I can make the transaction numbers different between Sales and Purchases by using a 1xxxxxxx for Purchases and 2xxxxxxx for sales but how can I lookup information in one workbook or another? I currently use INDEX MATCH with an IF for errors to lookup the transactions from a single sheet. |
#2
|
||||
|
||||
Hi
a sample wbook would help
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Hi
Thanks for your response. The workbooks are large, 15MB and being new to this I don't know how to get them to you. I have done to screen shots though. The Transaction page is the one that I want to make into 2 workbooks, 'Sales' and 'Purchases'. The stock record shows the formulae that I use to lookup data from the Transactions page. A 'Transaction Number' from either the 'Sales Transaction Workbook' or the 'Purchases Workbook' will be copied into the 'Sales Record' I want to change the formulae in the 'Sales Record' so that it will find a unique Transaction Number in either the 'Sales Transaction Workbook' or the 'Purchases Workbook' Perhaps there is a 'Work Around' that will do the same job. Thanks for your help |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Syncing data between two excel workbooks using multiple criteria | Merlot | Excel | 2 | 05-04-2016 06:41 AM |
Formulas transferring to other pages in workbooks, but data isn't. | Melissa Ames | Excel | 4 | 03-02-2016 01:58 PM |
Pull the data from different closed workbooks paste into master vba | manilara | Excel Programming | 2 | 11-19-2015 08:41 PM |
Office 2008 workbooks into Office 2011 workbooks | nfotx | Excel | 0 | 12-07-2014 04:22 PM |
PLEASE HELP in merging workbooks 07 | Micheleg | Excel | 3 | 05-19-2014 07:52 AM |