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.


--
Rich

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


"Rich" wrote in message
...
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.


--
Rich

Yes that has worked, thanks very much
--
Rich


"Bob Umlas" wrote:

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


"Rich" wrote in message
...
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.


--
Rich

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


"Rich" wrote:

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.


--
Rich

Wooooooooe,,,,

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

Sorry,
Jim


"Jim May" wrote:

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


"Rich" wrote:

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.


--
Rich