Multiple Worksheets formula - Help

[DeanH]

Excel 2003 on XP
I have a file that contains nearly one hundred worksheets.
Each Worksheet has been renamed with the city location it relates to.
All the worksheets are exactly the same.
On a new sheet, I wish to extract from each location sheet information from
a number of cells, these will always the the same cells.
I have a list of the worksheet names in column A (say), then in the
subsequent columns I wish to have the results of the cells.

What formula can I right that can be pasted down that picks up the worksheet
name in column A and returns the cell data per worksheet?

Obviously I can do this manually but with over 50 cells to extract from each
worksheet, I hope someone can assist me.
Hope this is clear enough.
Many thanks in advance
DeanH

 

[Dave Peterson]

With the worksheet names in A2:A101
and the cell addresses to retrieve in B1:AY1, you could put a formula like this
in B2:

=INDIRECT("'"&$A2&"'!"&B$1)

Drag down and drag across.

 

[DeanH]

Fantastic.
I have been playing with concatenate and & but the Indirect is new to me.
Many thanks and have a great weekend.
DeanH