Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Working with several tabs
Hey everybody, I have a small problem and hope there is a solution, so I came
to the experts. To start off I have a workbook with the first tab marked "Inventory" and it contains all the individual ingredients with columns A thru L and have different column headings. The next several tabs are my indivdual costing sheets for my recipes, and each tab has anywhere from 4 to 10 different costing sheets per tab. The individual costing sheet has the list of ingredients for that recipe, and it also has several columns from A to I, each of the ingredients are in no special order. The last column on the costing sheet is a number that is calculated on the sheet from a conversion factor number for changing the recipe size say from 4 portions to 10 portions, with each ingredient changing the amount of ounces to match the new portion size. I used VLookup on the costing sheets to insert the information I needed from the inventory sheet into the costing sheet per indivdual ingredient as it is entered into the recipe. Each ingredient is not on the same line number in each recipe, and may not even be in every recipe. The problem is I would like to add up the individual ingredients on the inventory sheet from all the costing sheets, so i know how many ounces I need to buy to make the recipes for the new portion sizes. The last column on the inventory sheet is the column I would need to put the formula in order to total all this up on. I am using Excel 2003 and Windows XP. If you need any more information I will do my best to get it you, and any help would be greatly appreciated. Thanks in advance for any help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Working with several tabs
Hi,
If I understand correctly, you need to combine the following two steps, for a tailor-made lookup() : 1. = Match(ingredient,referencelist,false) to find out if the ingredient is in the recipe and locate its line ... then, 2. = Index(table, match, column) where match is the formula described in point 1and column the column number of the criterion you are looking for ... Hope explanation is clear enough ... Cheers Carim |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Working with several tabs
Thanks for the information Carim, but I am not sure how to use the
information and insert it into my sheets. Will this be able to look at each of the costing sheets and look for the indivdual ingredient and add up all of the individual ingredients and give me the total for each? Thanks again Chefmike "Carim" wrote: Hi, If I understand correctly, you need to combine the following two steps, for a tailor-made lookup() : 1. = Match(ingredient,referencelist,false) to find out if the ingredient is in the recipe and locate its line ... then, 2. = Index(table, match, column) where match is the formula described in point 1and column the column number of the criterion you are looking for ... Hope explanation is clear enough ... Cheers Carim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
View tabs side by side? | Excel Discussion (Misc queries) | |||
Combining information from multiple tabs | Excel Worksheet Functions | |||
Combining multiple tabs into one. | Excel Discussion (Misc queries) | |||
Highlighted Reference Cell for a Working Cell | Excel Worksheet Functions | |||
Working spreadsheet highlighting function for Excel 2007 | Excel Worksheet Functions |