Referencing cell text in a lookup

[Guest]

I have a spreadsheet of 20 columns. Each column of data uses a lookup to
reference it's own worksheet. The column header for each column is the same
as the worksheet name.

For example
Column 1: Header '9th May', then =VLOOKUP($A1,'9th May'!$A$2:$D$21,4,FALSE)
Column 2: Header '16th May',then =(VLOOKUP($A1,'16th May'!$A$2:$D$21,4,FALSE)
and so on for each column

So for 20 columns I have to manually change the worksheet reference each time.

Is they a way to reference the text of the column header instead ie. replace
'9th May', with the text for column 1? This way I can set up the first column
and then autofill the remaining columns.

 

[Bob Umlas]

Try this:
=VLOOKUP($A1,INDIRECT("'"&$A1&"'!$A$2:$D$21"),4,FALSE)
Note the single quote in the first set of quotes & again before the !

 

[Guest]

Yes that has worked, thanks very much
--
Rich

Try this:
=VLOOKUP($A1,INDIRECT("'"&$A1&"'!$A$2:$D$21"),4,FALSE)
Note the single quote in the first set of quotes & again before the !

 

[Guest]

I'de name my First master sheet "Main"
with your subsequest sheets names in:
A3, B3, C3, D3

then in Sheet2 paste in:
Private Sub Worksheet_Activate()
ActiveSheet.Name = Sheets("Main").Range("A3").Value
End Sub

then in Sheet3 paste in:
Private Sub Worksheet_Activate()
ActiveSheet.Name = Sheets("Main").Range("B3").Value
End Sub

then in Sheet4 paste in:
Private Sub Worksheet_Activate()
ActiveSheet.Name = Sheets("Main").Range("C3").Value
End Sub

then in Sheet5 paste in:
Private Sub Worksheet_Activate()
ActiveSheet.Name = Sheets("Main").Range("D3").Value
End Sub

and so on...

 

[Guest]

Wooooooooe,,,,

Better forget my suggestion;
I was way OFF BASE on that one,,,

Sorry,
Jim