Dynamic Sheet Name in Vlookup

[John]

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.

 

[Bernie Deitrick]

John,

Try

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

HTH,
Bernie
MS Excel MVP

 

[John]

John,

Try

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

HTH,
Bernie
MS Excel MVP

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.

 

[JE McGimpsey]

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

 

[John]

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






- Show quoted text -

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

 

[Bernie Deitrick]

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

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






- Show quoted text -

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

 

[John]

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







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.