Summary of data from 20 sheets
 

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.

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

"Allan Skyner" wrote:

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.

look at these posts

http://tinyurl.com/4q2wl
http://tinyurl.com/5rodg
--
Don Guillett
SalesAid Software

"Allan Skyner" wrote in message
...
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.

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

-----Original Message-----
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.
.

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

"Allan Skyner" wrote in message
...
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.

Hi Jason

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

"Jason Morin" wrote:

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

-----Original Message-----
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.
.

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

-----Original Message-----
Hi Jason

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

"Jason Morin" wrote:

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

-----Original Message-----
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.
.


.

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

Thanks so much for taking time to help.

"Jason Morin" wrote:

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

-----Original Message-----
Hi Jason

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

"Jason Morin" wrote:

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

-----Original Message-----
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.
.


.