Hi,

You may also try this. Go to Insert Name Define and in the name box,
type sheets. in the refers to box, type
=TRANSPOSE(GET.WORKBOOK(1))&T(NOW()).

Now select a range of cells (select as many cells as there are sheets) and
enter the following array formula (Ctrl+Shift+Enter)

=MID(sheets,FIND("]",sheets)+1,255).

This will give you all the sheet names in the workbook. Please note that
this will ask you to Enable macros - click on yes.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Newfie809" wrote in message
...
Thank you for the information, I see what you mean if the sheets have a
different name which they do. I will keep working on it. Thanks again.

--
Newfie


"Bernard Liengme" wrote:

In E6 of Sheet1 thru Sheet4 I have these names: Brown, Smith, Jones
On summary sheet in A3 (but it could be in any cell) I entered
=INDIRECT("Sheet"&ROW(A1)&"!E6")
I copied this down the column to get
Brown
Smith
Jones

It get a bit more complex if the sheets do not have the names Sheet1,
Sheet2
.....
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Newfie809" wrote in message
...
Hi there,

I am looking for help with a spreadsheet formula,

I have 54 Worksheets and on Line C6 of each worksheet is the
Description
and
on
E6 of each worksheet is a Name. I would like set up another worksheet
that
will list the information on E6 from all the other worksheets to one
locations.

Sheet 1
C6 D6 E6
Manager Bill
Taylor
Assistant Manager Brenda
Davidson

Sheet 2
C6 D6 E6
Manager Eric
Bradley
Assistant Manager Monica
Davis

There is a lot of other information on each worksheet, but what I am
looking
for is to have the names of all the Managers from the 54 worksheets on
one
worksheet, and also the names of all the Assistant Manager from the 54
worksheets on one worksheet. Is this possible. Thank you

Newfie