| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excell 2007 Vlookup linking multiple tabs
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excell 2007 Vlookup linking multiple tabs
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" wrote: 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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excell 2007 Vlookup linking multiple tabs
"Sheeloo" wrote: 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" wrote: 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. Seeker Thanks for your reply, but I do not think that is what Im looking for. As I tried to explain in my original question Im 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excell 2007 Vlookup linking multiple tabs
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" wrote: "Sheeloo" wrote: 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" wrote: 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. Seeker Thanks for your reply, but I do not think that is what Im looking for. As I tried to explain in my original question Im 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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excell 2007 Vlookup linking multiple tabs
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" wrote: 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" wrote: "Sheeloo" wrote: 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" wrote: 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. Seeker Thanks for your reply, but I do not think that is what Im looking for. As I tried to explain in my original question Im 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excell 2007 Vlookup linking multiple tabs
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... "seeker" wrote: 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" wrote: 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" wrote: "Sheeloo" wrote: 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" wrote: 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. Seeker Thanks for your reply, but I do not think that is what Im looking for. As I tried to explain in my original question Im 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do I use vlookup in excell 2007? | Excel Discussion (Misc queries) | |||
| How to open MS Excell 2007 Sheet in MS Excell 2000??? | New Users to Excel | |||
| How do I use VLOOKUP to ref multiple workbooks with multiple tabs? | Excel Discussion (Misc queries) | |||
| Vlookup across multiple tabs | Excel Worksheet Functions | |||
| How to pull data out of an excell file with multiple tabs | Excel Worksheet Functions | |||
Linear Mode
