Vlookup and Sum across multiple tabs

foodiebill

New Member
Joined
Jan 24, 2018
Messages
3
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
 

foodiebill

New Member
Joined
Jan 24, 2018
Messages
3
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!!?
 

cooper645

Well-known Member
Joined
Nov 16, 2013
Messages
521
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

New Member
Joined
Jan 24, 2018
Messages
3
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

Well-known Member
Joined
Nov 16, 2013
Messages
521
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

Board Regular
Joined
Nov 10, 2013
Messages
114
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
 

Forum statistics

Threads
1,082,126
Messages
5,363,315
Members
400,725
Latest member
excelingtolearn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top