Summary of data from 20 sheets

A

Allan Skyner

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.
 
S

Simon Shaw

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...
 
J

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
 
J

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
 
A

Allan Skyner

Hi Jason

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

Allan Skyner

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 [email protected]

Thanks so much for taking time to help.
 
Top