Working with several tabs

[Guest]

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.

 

[Carim]

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

 

[Guest]

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