1

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

https://www.dropbox.com/s/pf1wzsa7ruhjzc1/stockSystem.xlsx?dl=0

so that you can see the problem more clearly.

If someone could point me in the right direction it would be appreciated.

Thanks, Luke

  • 1
    Any example data should be in the question. Please edit your question to include some example (mock) data (before and after). See Format Text as a Table for a web utility that will help you to create a nice data table you can paste into your question. Upload any image(s) to Imgur and you will get link(s) you can share. Edit your question to include the link(s) and someone with sufficient reputation will inline the image(s) for you. – DavidPostill Feb 4 '17 at 12:26
2

Since your sheet is saved as .xlsx I believe you are using Excel 2007 or above. Basically while there’s an error in your VLOOKUP function, in principle you may not get the desired results if you use VLOOKUP or INDEX MATCH in your case.

What you are attempting to do is lookup for an Item in 'Transaction' & 'Received' Sheets with two conditions, as per your inline comments therein.

1 - Item name should match

2 - Date should be less than or equal to current date

With these two conditions met, you need to sum the Quantity.

Apparently I am not sure why you need to match date, usually because there should not be any futuristic transactions entered therein.

However this can be achieved using SUMIFS function that is available in Excel 2007 onward.

See the screenshots below.

In your Cell B6 of Sheet named Stock put the following formula and drag it down. In this example I have taken a small range of cells say A4 thru A14, however it can be modified as per your sheet.

=SUMIFS(Received!$E$4:$E$14,Received!$A$4:$A$14,"<="&NOW(),Received!$D$4:$D$14,A6)-SUMIFS(Transactions!$E$4:$E$15,Transactions!$A$4:$A$15,"<="&NOW(),Transactions!$D$4:$D$15,A6)

enter image description here

enter image description here

enter image description here

2

Rohit's answer is a good one. Particularly the second option as its a much lighter touch and still returns the desired result.

Another option it use INDEX and MATCH instead. In this way, you can eliminate VLOOKUPs first column lookup value requirement -

=INDEX(Received!$E$4:$E$10, MATCH(A6, Received!$D$4:$D$10, 0))

1

Your VLOOKUP formula is =VLOOKUP(A6,Received!A4:F27,5,FALSE). However it is looking for 'Biscuits' in Column A but that item is in fact in column D. VLOOKUP Looks up for the value in the first column of the table.

Interchange your columns A & D in your sheet 'Received' if that works fine with you and VLOOKUP should work fine.

Alternatively change the formula to =VLOOKUP(A6,Received!D4:F27,2,FALSE) in case first 3 columns do not matter and it will search correctly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.