Changing worksheet reference in a VLOOKUP

[Mark]

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

 

[M Kan]

Here's a formula structure I've used before:

=IF(ISERROR(INDIRECT(D$3&"!"&$C4)),"",INDIRECT(D$3&"!"&$C4))

D$3 is a column header
$C4 is a cell address

The INDIRECT command changes a text string into a variable string (e.g., a
string of text that happens to look like a sheet and cell address into a cell
address)

The ISERROR piece is to remove #REF errors in the case a sheet doesn't
exist. In the case a sheet doesn't exist, it just returns a blank.

My recommendation is the link directly to your target cell first, and then
build the INDIRECT formula.

 

[T. Valko]

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)

 

[Mark]

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]

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)

 

[Mark]

Yes. You nailed it. Works perfectly! Thank you

Mark

 

[T. Valko]

You're welcome. Thanks for the feedback!