Vlookup and Sum across multiple tabs

[foodiebill]

So I'm a chef and not great at spreadsheets, hoping for some help!

Basically I have 15 tabs, one recipe per a tab with ingredients in one column and the corresponding kg in the next column. I am trying to create a summary - shopping list tab. I've manually listed all the individual ingredients across the 15 recipes into this tab and would love to know if there is a way of searching across all the recipe tabs by ingredient and adding these together to go into the shopping list.

I hope that makes sense and thanks in advance.

Cheers

Billy

 

[Johnny Thunder]

This is how you would do it.

Its a little read, but it will work in your situation. The instructions are very detailed.

Link: https://www.extendoffice.com/documents/excel/2450-excel-vlookup-in-multiple-sheets.html

 

[foodiebill]

This is how you would do it.

Its a little read, but it will work in your situation. The instructions are very detailed.

Link: https://www.extendoffice.com/documents/excel/2450-excel-vlookup-in-multiple-sheets.html

Thanks Johnny, have been through this, but I don't want to add all the info from the individual sheets into one summary sheet, I'm trying to select specific matching cells for instance those called sugar which appears in 4 of the 15 tabs and add the corresponding 4 sugar (kg) figures together within the summary tab. There are 120 different ingredients to pull the figures through for.

Unless I've missed something which is entirely possible!!?

 

[Johnny Thunder]

you could adapt this to get what you want. you would change the countif to a Sumif statement.

Link: https://exceljet.net/formula/search-multiple-worksheets-for-value

 

[cooper645]

Bill, the solution posted would work for you, however, how often do you change the recipes?

also what are your fifteen other tabs called? Are they named after the dish?
if you change the recipes regularly a formula approach will be time consuming.

If the recipes dont change and nor and not the list of ingredients then you should be fine, however I suspect differently, which is why a macro solution would be your best opinion.

Please advise ad to the above points and I would be happy to help you develop this further.

Kindest regards,

Coops

 

[foodiebill]

Bill, the solution posted would work for you, however, how often do you change the recipes?

also what are your fifteen other tabs called? Are they named after the dish?
if you change the recipes regularly a formula approach will be time consuming.

If the recipes dont change and nor and not the list of ingredients then you should be fine, however I suspect differently, which is why a macro solution would be your best opinion.

Please advise ad to the above points and I would be happy to help you develop this further.

Kindest regards,

Coops

Hi Coops,

Thanks for your questions. Its actually the latter. This is for a one off big event so the 15 recipes will remain but the end numbers may change. I have a table set up with the tab names in as they are named after the dishes.

Thanks for your help!

Cheers

 

[cooper645]

This entered in Cell B1 of your summary sheet

=SUM(
IF(ISNA(LOOKUP($A1,Sheet1!A:A)),"0",VLOOKUP($A1,Sheet1!A:B,2,0)),
IF(ISNA(LOOKUP($A1,Sheet2!A:A)),"0",VLOOKUP($A1,Sheet2!A:B,2,0)),
IF(ISNA(LOOKUP($A1,Sheet3!A:A)),"0",VLOOKUP($A1,Sheet3!A:B,2,0)),
IF(ISNA(LOOKUP($A1,Sheet4!A:A)),"0",VLOOKUP($A1,Sheet4!A:B,2,0)),
IF(ISNA(LOOKUP($A1,Sheet5!A:A)),"0",VLOOKUP($A1,Sheet5!A:B,2,0)),
IF(ISNA(LOOKUP($A1,Sheet6!A:A)),"0",VLOOKUP($A1,Sheet6!A:B,2,0)),
IF(ISNA(LOOKUP($A1,Sheet7!A:A)),"0",VLOOKUP($A1,Sheet7!A:B,2,0)),
IF(ISNA(LOOKUP($A1,Sheet8!A:A)),"0",VLOOKUP($A1,Sheet8!A:B,2,0)),
IF(ISNA(LOOKUP($A1,Sheet9!A:A)),"0",VLOOKUP($A1,Sheet9!A:B,2,0)),
IF(ISNA(LOOKUP($A1,Sheet10!A:A)),"0",VLOOKUP($A1,Sheet10!A:B,2,0)),
IF(ISNA(LOOKUP($A1,Sheet11!A:A)),"0",VLOOKUP($A1,Sheet11!A:B,2,0)),
IF(ISNA(LOOKUP($A1,Sheet12!A:A)),"0",VLOOKUP($A1,Sheet12!A:B,2,0)),
IF(ISNA(LOOKUP($A1,Sheet13!A:A)),"0",VLOOKUP($A1,Sheet13!A:B,2,0)),
IF(ISNA(LOOKUP($A1,Sheet14!A:A)),"0",VLOOKUP($A1,Sheet14!A:B,2,0)),
IF(ISNA(LOOKUP($A1,Sheet15!A:A)),"0",VLOOKUP($A1,Sheet15!A:B,2,0)))

will work for your needs, providing that your sheets are named Sheet1, Sheet2, Sheet3 and so on.
And your ingredients are listed from cell A1 down on each sheet and the numeric values are in cell B1 down on each sheet.


Hope this helps you out.

Coops

 

[Abdulkhadar]

This is how you would do it.

Its a little read, but it will work in your situation. The instructions are very detailed.

Link: https://www.extendoffice.com/documents/excel/2450-excel-vlookup-in-multiple-sheets.html

Hello Sir,
to lookup the value from the array formula i.e
{=VLOOKUP($B$1,INDIRECT("'"&INDEX(Sheetlist,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheetlist&"'!$C$7:$AL$100"),$B$1)>0),0))&"'!$C$7:$AL$100"),2,FALSE)}
it takes long time, any VBA to get the value from 22 sheets and shows entire row.

thanks in advance