Biff,

Thanks for this. I may however have been not clear in what I was looking
for. You have the right idea but my worksheet names are not chronologically
ordered. My worksheets are all named for company codes. For example WS1
could be 200, WS2 could be 210 and WS3 could be 300 and on up to 3000,
although there are not 3000 worksheets obviously. There are however about 30
of them, all with the same data range within but different numbers
(worksheet names) based on company code. So using the column reference will
not work in this case.

So, are there any other options available to me? Again here is how my data
is laid out:

Row A1 to A3000 are account numbers and each column represents a company
code. My thinking being if I setup the VLOOKUP for the first column then
somehow copying the formula across it will pick up a value to represent the
worksheet from a cell within each column that had the lookup info.

I can send you the spreadsheet if the visual is more helpful.

Thanks

Mark


"T. Valko" wrote in message
...
Try this:

=VLOOKUP($A8,INDIRECT("'"&COLUMN(HL1)&"'!B8:C1274" ),1)

Copy across as needed.

As you copy across the formula will evaluate to:

=VLOOKUP($A8,'220'!$B$8:$C$1274,1)
=VLOOKUP($A8,'221'!$B$8:$C$1274,1)
=VLOOKUP($A8,'222'!$B$8:$C$1274,1)
=VLOOKUP($A8,'223'!$B$8:$C$1274,1)


--
Biff
Microsoft Excel MVP


"Mark" wrote in message
...
Using Excel 2007 and XP Pro SP2.

I am using a VLOOKUP formula which is working fine, however I have about
20 worksheets within my workbook each containing unique values (same data
ranges however). Is it possible to change the worksheet reference in the
example below to be a value in a column? In other words if I have columns
that represent each worksheet, can I place a cell reference in that
column to call the respective worksheet? I am looking to formulaically
change the value '220' so if I copy across it will pick up the next
worksheet value of '221', '222', '223', etc. This way I can copy that
same VLOOKUP command across a number of columns and it will automatically
grab the data from that worksheet.

This formula would reside in a separate worksheet in the same excel file.

=VLOOKUP($A8,'220'!$B$8:$C$1274,1)

So if I copy across the function would look like this for each new
column:
Cell A1=VLOOKUP($A8,'221'!$B$8:$C$1274,1)
Cell B2=VLOOKUP($A8,'222'!$B$8:$C$1274,1)
Cell C2=VLOOKUP($A8,'223'!$B$8:$C$1274,1)

This would go on for about 30 columns
etc.


Thanks

Mark