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.