Summary of data from 20 sheets

[Guest]

I have a workbook with 20 sheets (countries) all formatted the same,
contained inj the sheets in cells A74;A90 are names of suppliers and the same
supplier name could appear in any of those cells on any of the sheets.
I'm trying to build a summary sheet so I can select a suppliers name in one
cell and then in various cells below the names of the countries (sheets)
appears.

 

[Guest]

Have you tried the SUMIF function?

Reserve say cell A1 on the summary sheet for the supplier name

on the summary sheet, put in a sumif for each country using:

=SUMIF('sheetname'!A74:A90,A1,'sheetname'!B74:B90)

assuming the data to be summarized is in the B column...

 

[Don Guillett]

look at these posts

http://tinyurl.com/4q2wl
http://tinyurl.com/5rodg

 

[Jason Morin]

With the supplier name in A1 and a list of all sheet
names (countries) in E1:E20, insert this into A2, press
ctrl/shift/enter, and copy down to A21:

=INDEX($E$1:$E$20,SMALL(IF(COUNTIF(INDIRECT
("'"&$E$1:$E$20&"'!A74:A90"),$A$1),ROW(INDIRECT
("1:20"))),ROW()-1))

HTH
Jason
Atlanta, GA

 

[JulieD]

Hi

apart from the fact that doing this for 20 sheets is quite labourious - i
think Data / Consolidation (with all three boxes ticked) will give you what
you want (try it on three sheets first to see).

Cheers
JulieD

 

[Guest]

Hi Jason

Thanks for the speedy reply but I get an error message that there is a
problem with the formula.

 

[Jason Morin]

Make sure to press ctrl/shift/enter after inserting the
first formula. Also, some cells with error out with #NUM!
which is OK. You can hide those if you wish. Make sure
that the list of worksheet names in E1:E20 typed in
correctly.

I can send you an example workbook if you wish.

Jason

 

[Guest]

Hi Jason

I've tried again and ensured that all details are correct but still return
an error message with the formula. It would be most helpful if you could send
me a sample workbook, my email address is (e-mail address removed)

Thanks so much for taking time to help.