Adding Values Across Spreadsheets

[Donalbain]

Hi,
Having done about all the Googling a boy can take, I am still unable to
find out how to do this, so I figured I would ask your goodselves.

I have a workbook with a number of pages. Each page represents the
formulation of a particular product. There is also a page that acts as
a summary of all the formulas(formulae?)

Example (hoping that googlegroups doesnt mess up the format)

First an individual formula

ProductID ProductName AmountOfProduct(kg)
1 Water 3
5 Salt 1


Then another individual formula page

ProductID ProductName AmountOfProduct(kg)
3 Cheese 5
1 Water 7
9 Milk 3



Now, I want the summary page (given the data above) to look like this

ProductID ProductName AmountOfProduct(kg)
1 Water 10
3 Cheese 5
9 Milk 3


Obviously, it has added the total amount of cheese and displayed that,
added the total amount of milk and displayed that. Its easy here, but I
cant do it in real life. The problem is that there is no particular
order to the ingredients list and not all ingredients show up on all
sheets.

If anyone is able to help, I would be ever so grateful and I would
remain employed for at least a week longer.

 

[JulieD]

Hi

check out data / consolidation,

use a blank sheet for the summary (initially anyway)
click on a cell and choose data / consolidation
check the three tick boxes and then click in the reference line
now go to the first worksheet and highlight the whole area (including the
ProductID row & all three columns) then click ADD
repeat for the other pages, don't worry if the ranges aren't the same

once you've done all the pages, click OK

this will match up the ProductID numbers and total the amounts -
unfortunately it won't fill in the product names for you, but this could
easily be populated by using a VLOOKUP formula if you have all the product
IDs & product names listed on another page somewhere.

hope this helps
Cheers
JulieD

 

[RagDyeR]

Enter this in C2 of your main sheet, where B2:B10 contain the Product names:
=SUMPRODUCT((Sheet1!$B$2:$B$10=B2)*Sheet1!$C$2:$C$10)+SUMPRODUCT((Sheet2!$B$
2:$B$10=B2)*Sheet2!$C$2:$C$10)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Hi,
Having done about all the Googling a boy can take, I am still unable to
find out how to do this, so I figured I would ask your goodselves.

I have a workbook with a number of pages. Each page represents the
formulation of a particular product. There is also a page that acts as
a summary of all the formulas(formulae?)

Example (hoping that googlegroups doesnt mess up the format)

First an individual formula

ProductID ProductName AmountOfProduct(kg)
1 Water 3
5 Salt 1


Then another individual formula page

ProductID ProductName AmountOfProduct(kg)
3 Cheese 5
1 Water 7
9 Milk 3



Now, I want the summary page (given the data above) to look like this

ProductID ProductName AmountOfProduct(kg)
1 Water 10
3 Cheese 5
9 Milk 3


Obviously, it has added the total amount of cheese and displayed that,
added the total amount of milk and displayed that. Its easy here, but I
cant do it in real life. The problem is that there is no particular
order to the ingredients list and not all ingredients show up on all
sheets.

If anyone is able to help, I would be ever so grateful and I would
remain employed for at least a week longer.

 

[Donalbain]

Hi

check out data / consolidation,

Thanks. That worked wonderfully!

 

[JulieD]

Hi

i'm glad and thanks for the feedback.

Cheers
JulieD