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


.