You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Mark" wrote in message
...
Yes. You nailed it. Works perfectly! Thank you

Mark


"T. Valko" wrote in message
...
If each column represents a company code is that code number the column
header?

..........A..........B..........C..........D
1...................208......200.......222

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

Copied across you'll get:

=VLOOKUP($A8,'208'!B8:C1274,1)
=VLOOKUP($A8,'200'!B8:C1274,1)
=VLOOKUP($A8,'222'!B8:C1274,1)


--
Biff
Microsoft Excel MVP


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