Dynamic Sheet Name in Vlookup
 

I have a table that is doing some vlookups from another workbook. The
problem i have is that for each column the worksheet name is named
differently. So, basically I have data a table like the one below:

HU SS PL .....
1
2
3
4


Under the HU column, the vlookup needs to pull from the HUTP
worksheet. Under the SS column, it needs to pull from the SSTP
worksheet, etc.

=VLOOKUP($A3,'S:\mfg\CFM\Reports\TOC_Reports\[Targets.xls]HUTP'!$H
$8:$IV$187,12,FALSE)

Any ideas on how to do this without having to change the sheet in
every column and instead pull it from the column header? IE, I want a
function that looks something like this (but this obviously doesn't
work):

=VLOOKUP($A2,'S:\mfg\CFM\Reports\TOC_Reports\[Targets.xls](A$1 &
"TP")'!$H$8:$IV$187,2,FALSE)

Thanks in advance.

Dynamic Sheet Name in Vlookup
 

John,

Try

=VLOOKUP($A3,INDIRECT("'S:\mfg\CFM\Reports\TOC_Rep orts\[Targets.xls]" & A$1 &
"TP'!$H$8:$IV$187"),12,FALSE)

HTH,
Bernie
MS Excel MVP


"John" wrote in message
...
I have a table that is doing some vlookups from another workbook. The
problem i have is that for each column the worksheet name is named
differently. So, basically I have data a table like the one below:

HU SS PL .....
1
2
3
4


Under the HU column, the vlookup needs to pull from the HUTP
worksheet. Under the SS column, it needs to pull from the SSTP
worksheet, etc.

=VLOOKUP($A3,'S:\mfg\CFM\Reports\TOC_Reports\[Targets.xls]HUTP'!$H
$8:$IV$187,12,FALSE)

Any ideas on how to do this without having to change the sheet in
every column and instead pull it from the column header? IE, I want a
function that looks something like this (but this obviously doesn't
work):

=VLOOKUP($A2,'S:\mfg\CFM\Reports\TOC_Reports\[Targets.xls](A$1 &
"TP")'!$H$8:$IV$187,2,FALSE)

Thanks in advance.

Dynamic Sheet Name in Vlookup
 

On Jun 5, 10:22*am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
John,

Try

=VLOOKUP($A3,INDIRECT("'S:\mfg\CFM\Reports\TOC_Rep orts\[Targets.xls]" & A$1 &
"TP'!$H$8:$IV$187"),12,FALSE)

HTH,
Bernie
MS Excel MVP

"John" wrote in message

...



I have a table that is doing some vlookups from another workbook. *The
problem i have is that for each column the worksheet name is named
differently. *So, basically I have data a table like the one below:

* * *HU * * SS * * PL * *.....
1
2
3
4

Under the HU column, the vlookup needs to pull from the HUTP
worksheet. *Under the SS column, it needs to pull from the SSTP
worksheet, etc.

=VLOOKUP($A3,'S:\mfg\CFM\Reports\TOC_Reports\[Targets.xls]HUTP'!$H
$8:$IV$187,12,FALSE)

Any ideas on how to do this without having to change the sheet in
every column and instead pull it from the column header? *IE, I want a
function that looks something like this (but this obviously doesn't
work):

=VLOOKUP($A2,'S:\mfg\CFM\Reports\TOC_Reports\[Targets.xls](A$1 &
"TP")'!$H$8:$IV$187,2,FALSE)

Thanks in advance.- Hide quoted text -

- Show quoted text -

It gives me a #REF error when I try to use it. Even tried just using
indirect to return set cell and was unable to do so.

Dynamic Sheet Name in Vlookup
 

To use INDIRECT() the target workbook needs to be open.

In article
,
John wrote:

It gives me a #REF error when I try to use it. Even tried just using
indirect to return set cell and was unable to do so.

Dynamic Sheet Name in Vlookup
 

On Jun 5, 12:47*pm, JE McGimpsey wrote:
To use INDIRECT() the target workbook needs to be open.

In article
,



*John wrote:
It gives me a #REF error when I try to use it. *Even tried just using
indirect to return set cell and was unable to do so.- Hide quoted text -

- Show quoted text -

Ah, that's the problem then. Anyway to do it without having the
workbook open?

Dynamic Sheet Name in Vlookup
 

John,

You can use a macro to create formulas that link to the file based on the
values of the headers cells - or you can simply copy the formula and edit
it to include the new worksheet name.

Bernie

"John" wrote in message
...
On Jun 5, 12:47 pm, JE McGimpsey wrote:
To use INDIRECT() the target workbook needs to be open.

In article
,



John wrote:
It gives me a #REF error when I try to use it. Even tried just using
indirect to return set cell and was unable to do so.- Hide quoted text -

- Show quoted text -

Ah, that's the problem then. Anyway to do it without having the
workbook open?

Dynamic Sheet Name in Vlookup
 

On Jun 5, 5:59*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
John,

You can use a macro to create formulas that link to the file based on the
values of the headers cells - or *you can simply copy the formula and edit
it to include the new worksheet name.

Bernie

"John" wrote in message

...
On Jun 5, 12:47 pm, JE McGimpsey wrote:

To use INDIRECT() the target workbook needs to be open.

In article
,

John wrote:
It gives me a #REF error when I try to use it. Even tried just using
indirect to return set cell and was unable to do so.- Hide quoted text -

- Show quoted text -

Ah, that's the problem then. *Anyway to do it without having the
workbook open?

OK. Was hoping to avoid having to do either of those two but it
appears it's down to that. Thanks for the help.