Compiling Data from various worksheets

**smithtronics** · 10-26-2011, 01:36 AM

I have a workbook that has 3 worksheets in it, two worksheets have ingredients of recipes in them, say things like grapes, flax, flour, sugar etc, the same worksheet also has produced goods like bread made of flour and other ingredients, and soda, and pies, and cakes. the list of items increase as my database increases, and the 3rd worksheet is for listing the total list of ingredients, and in the next column of this worksheet has the total amount of ingredient. like say flour, say I wanted to produce all my recipes together at the same time, how much flour ow grapes, or flax would I need to have on hand to do this, 50 gallons of flour, or 300 cups of flax seed etc. I would want this 3rd worksheet to dynamically change as my ingredients change, so I would need this 3rd page to search everywhere in these two worksheets for a given word say "flour" or "Flax" and every time it found this ingredient it would change, and so if I added another recipe it would automatically update it.
I need help in the formula to do this. I know they use this type of feature in book keeping to pull financials or hours from an employee roster or balance sheet.

Can anyone Please help me.

**arlu1201** · 10-26-2011, 03:39 AM

It will be good if you can attach a sample workbook with you various sheets and explain your requirement with the help of that.

**smithtronics** · 10-26-2011, 05:21 PM

Thanks for your reply, here is the excel file, there is two worksheets, one with the data, and the other with the results needed.
Here is my need:
I have ingredients and products that I produce such as flour from wheat or corn flour from corn. and raisins are made of grapes, but there are many recipes that are made with flour, so on my ingredient list on my second worksheet I want it to automatically look for the word flour in all the the recipes, and then under flour it will state how much flour I need in a 24 hour period to keep all my facilities full.

The flour line would also need to dynamically change when new recipes are added.

I hope some one can help me.

thanks to any help offered.

**arlu1201** · 10-27-2011, 06:07 AM

Please explain columns C, D and E in the attached file. Also, i see numbers in column A, what does that signify? Based on your answer, we can proceed further with the calculations.

**smithtronics** · 10-27-2011, 03:54 PM

Column E is a calculation of C and D, Column E is the number I need to use in my addition of acorn squash, flour etc. and column A is not part of the formula, Column b is the value I need added, or searched.

**smithtronics** · 10-27-2011, 03:56 PM

I tried this formula =SUMPRODUCT((B5:B45="Mushroom")*(E5:E45)) but when column E sees the names like "Daily:" it freaks out and dont reply with a comment, I need it to ignore it. if possible.