Muliple Worksheet Lookup

[DMc2004]

Hi

Currently the problem is:

1) I have a varied amount of worksheets. Anything from 1 to 80.
2) On a Summary Worksheet I have table that looks like:

Month 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
Jan
Feb
Mar
Apr
Etc

Column A, months can be anything.

Each worksheet tab is named using the same convention as the worksheet tab,
so there could be a worksheet called Jan, Feb or Mar.


I need to contruct a Vlookup that looks on the relevent worksheet tab and
and looks for the heading of the data item on the first row of the summary
sheet and returns the matching item to the summary sheet.

I have used indirect, Address before but canont seem to get any further. Any
ideas.

I do not want to use VBA, only Excel Functions.

Regards

 

[Pete_UK]

Try something like this in B2:

=INDIRECT($A2&"!"&CHAR(B$1+65)&1)

Then copy across and down as necessary.

Not sure how you can have 80 worksheets if they are all named after a
month - is that what you mean by "months can be anything"? i.e. the
names can be anything? If that is the case and you might have spaces
in the names, then you will need to do this:

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

Note the apostrophes. This will work for up to 25 columns.

Hope this helps.

Pete