Excell 2007 Vlookup linking multiple tabs

[seeker]

I have a spread sheet that I created to upscale and downscale food recipes. I
have linked an “order page†to individual recipes so if I want to increase or
decrease the recipe I only have to enter the amount of cases I want to
produce and it flows to each sheet. The last page I have is an inventory
page. I have used the V-lookup formula so that each individual recipe looks
at the inventory page to get its price per pound. For example =VLOOKUP (B13,
Inventory, 11, FALSE)*E13. B13 is the name of an ingredient, then it looks
at the inventory page finds the exact ingredient, then looks 11 columns over
and uses that price per pound.
I am trying to create a page that will add up all the ingredients from each
individual recipe so I can use it to order ingredients. I have used the
following =VLOOKUP (B9,'Sweet Orange Chili Mustard'! B13:E18, 4, FALSE)
this works great for one sheet, but when I try to add another sheet either by
using a , or putting the additional sheets in a () it returns a N/A. Anybody
have any ideas.

 

[Sheeloo]

I am not sure what you want to do.

In case you want to add the cell A1 across many sheets then use this
=Sum(Sheet1:Sheet10!A1)

This will add up the cell A1 on each sheet which is between Sheet1 and
Sheet10. By 'between' I mean on the tabs at the bottom of the workbook. If
you have only two sheets the first one named Sheet1 and the second Sheet10
then it will add up only these two. Now if you insert many sheets in between
(names don't matter) then it will add up those too.
If you move one to the right of Sheet10 (or to the left of Sheet1) then that
sheet will not be added.

Lookup Consolidate in HLEP...

 

[seeker]

I am not sure what you want to do.

In case you want to add the cell A1 across many sheets then use this
=Sum(Sheet1:Sheet10!A1)

This will add up the cell A1 on each sheet which is between Sheet1 and
Sheet10. By 'between' I mean on the tabs at the bottom of the workbook. If
you have only two sheets the first one named Sheet1 and the second Sheet10
then it will add up only these two. Now if you insert many sheets in between
(names don't matter) then it will add up those too.
If you move one to the right of Sheet10 (or to the left of Sheet1) then that
sheet will not be added.

Lookup Consolidate in HLEP...

Seeker
Thanks for your reply, but I do not think that is what I’m looking for. As
I tried to explain in my original question I’m using Vlookup on each
individual recipe page that looks at the inventory page to find current
pricing. I.e. =VLOOKUP (B13, Inventory, 11, FALSE)*E13. B13 is an
ingredient such as Sugar, and then it looks at the inventory page finds the
word sugar and looks in the 11th column for the price. All this works great
because each individual recipe sheet is only looking at one page “inventoryâ€
and then the name of the ingredient. What I am trying to do is create a
“combined page†so I can have one piece of paper that will give me the total
of all the ingredients used throughout all the different recipes. I have
named that page Combined. In column B I have listed all the names of the
different ingredients. What I would like to happen is excel to look at each
individual recipe, find the matching ingredient, look at the column that has
the ingredient amount and show me that amount on the “combined†sheet. There
might be 5 separate recipes containing Sugar. I want the combined page to
add them all up for each one. This formula works for one sheet. =VLOOKUP
(B9,'Sweet Orange Chili Mustard'! B13:E18, 4, FALSE) , but I do not know
how to link additional sheets. I hope I have explained this well enough.
Thanks for your help.

 

[Sheeloo]

Understood your requirements...

How many recipes (sheets) do you have? Must be many...

I can't think of a simple way (without enumerating all sheets) to get what
you want...

 

[seeker]

Currently 9, but there could be many more. You said you understood what my
requirements are but I thought I would put this out as well. The more I
thought about it, all I want is basically the inverse of what I a currently
doing. Instead on many sheets looking to one, I want one to look at many.

Regards.

 

[Sheeloo]

I don't think that is possible without using VBA.

Suggest you post your question in the terms below so that someone else may
suggest an answer...