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.

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

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

Attached Images

	Stock Record.jpg (238.8 KB, 13 views)
	Transaction page.jpg (282.3 KB, 13 views)