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