VLOOKUP or MATCH

[Jaladino]

Hello,
I entered in a workbook 5 sheets containing 5 different
recipes in each sheet. each sheet contained, in column a; volume of
ingredient,
column b name/description of ingredient. in the last sheet (6) I listed, in
column a, all ingredients used in all recipes (sheets), and in column b I
want to have a formula which will return how much (sum) of that
ingredient I will need based on a table in sheet 6 where I can select
(via drop down list) which and/or how many recipes (sheets) I am selecting.

Thanks in advance,

Jay

 

[David McRitchie]

Hi Jay,
Can you make this easier to understand what you have
and what you want. Should we care that you have the first
five sheets (five recipes) for a product. Do you have a
drop down or are you trying to create a drop down.

If the ingredients in Sheet 1 to 5 and the totals in Sheet 6
are all in the same rows, the totals in Sheet6 can be
written as follows: (these are called 3D references in HELP)

sheet6 cell D3:
=SUM(sheet1:sheet5!D3)
which is the equivalent of
=sheet13 + sheet23 + sheet3:d3 + sheet4:d3 + sheet5:d3

Note the physical order of the worksheet tabs must be
Sheet1 ... Sheet5 and all sheets inclusively in the range will be included.
i.e.
=SUM('Sheet one:sheet 5'!D3))

That leaves a subject of VLOOKUP or MATCH which doesn't appear
to be mentioned in the question. Since it probably is significant see
http://www.mvps.org/dmcritchie/excel/vlookup.htm
http://www.cpearson.com/excel/lookups.htm