Here is the issue:
I am building a stock inventory system. It has 3 sheets: Transactions (where the transactions made are entered), Received (where new stock received is entered), and Stock (where the costs and quantity in stock are shown).
The Transactions and Received pages use dropdowns to select items from the product range (which are stored on the Backend sheet).
On the stock page, say in the "Biscuits" Quantity In Stock cell, I would like to lookup any occurrences of "Biscuits" on the transactions sheet from today or before, getting the quantities of each. I then need to do the same with the Received sheet and minus the two numbers to get the actual Quantity In Stock.
I have looked at numerous guides on using the VLOOKUP function with multiple sheets however they all seem to return a value on the same sheet as the lookup value, rather than on a different sheet. This is resulting in a #N/A message every time.
The sheet can be found here
so that you can see the problem more clearly.
If someone could point me in the right direction it would be appreciated.