Vlookup Questing

Is it possible to have a vlookup reference a cell in the same spreadsheet to determine the name of the outside source the vlookup will be using? Example follows

Instead of:

=vlookup(a1,'C:\Users\[October.xls]Template'!$A:$B,2,FALSE)

I want to use

=vlookup(a1,'C:\Users\["=a2".xls]Template'!$A:$B,2,FALSE)

Where A2 = November?

-Kurt

Originally Posted by Kurt_v

Is it possible to have a vlookup reference a cell in the same spreadsheet to determine the name of the outside source the vlookup will be using? Example follows

Instead of:

=vlookup(a1,'C:\Users\[October.xls]Template'!$A:$B,2,FALSE)

I want to use

=vlookup(a1,'C:\Users\["=a2".xls]Template'!$A:$B,2,FALSE)

Where A2 = November?

-Kurt

try:

=VLOOKUP(A1,INDIRECT("'C:\Users\["&A2&".xls]Template'!$A:$B"),2,FALSE)

Note the single quote after the first double quote... it might be hard to see.

Scott

Originally Posted by Maistrye

try:

=VLOOKUP(A1,INDIRECT("'C:\Users\["&A2&".xls]Template'!$A:$B"),2,FALSE)

Note the single quote after the first double quote... it might be hard to see.

Scott

Scott, thanks for the reply. If I can get this to work I will be happy. But unfortunatly its not working quite yet and it may be because I accidently lied. I actually dont need to replace the spreadsheet name, but instead the worksheet it is looking at.

So the formula needs to point to a specific tab inside the spreadsheet using a pointer cell from in the same spreadsheet the vlookup is in (just to reiterate). Using your formula I tried:

=VLOOKUP(D8,INDIRECT("'N:\Departments\Outbound\Shared\Employee Development Meetings\[VX Tracker.xls]"&F6&"'!$A:$F),5,FALSE)

D8 being the cell I need to find.
F6 being the month that I want to look in.

For example F6 = October. The normal formual looks as follows:

=IF(ISERROR(VLOOKUP(D8,'[VX Tracker.xls]VX TRACKER October'!$A:$F,5,0)),0,VLOOKUP(D8,'[VX Tracker.xls]VX TRACKER October'!$A:$F,5,0))

Dont worry about the IF and ISERROR part. If I (we/you) can figure out the vlookup part I can easily place it in the IF statment.

Thanks.

-Kurt

Originally Posted by Kurt_v

Scott, thanks for the reply. If I can get this to work I will be happy. But unfortunatly its not working quite yet and it may be because I accidently lied. I actually dont need to replace the spreadsheet name, but instead the worksheet it is looking at.

So the formula needs to point to a specific tab inside the spreadsheet using a pointer cell from in the same spreadsheet the vlookup is in (just to reiterate). Using your formula I tried:

=VLOOKUP(D8,INDIRECT("'N:\Departments\Outbound\Shared\Employee Development Meetings\[VX Tracker.xls]"&F6&"'!$A:$F),5,FALSE)

D8 being the cell I need to find.
F6 being the month that I want to look in.

For example F6 = October. The normal formual looks as follows:

=IF(ISERROR(VLOOKUP(D8,'[VX Tracker.xls]VX TRACKER October'!$A:$F,5,0)),0,VLOOKUP(D8,'[VX Tracker.xls]VX TRACKER October'!$A:$F,5,0))

Dont worry about the IF and ISERROR part. If I (we/you) can figure out the vlookup part I can easily place it in the IF statment.

Thanks.

-Kurt

The only thing I can see for sure is that at the end of the $A$F there should be a double quote (").

=VLOOKUP(D8,INDIRECT("'N:\Departments\Outbound\Shared\Employee Development Meetings\[VX Tracker.xls]"&F6&"'!$A:$F"),5,FALSE)

Scott

I don't know if it's still true on newer versions, but up through Excel 2002, INDIRECT could only pull data from open workbooks (see help on the INDIRECT function). Not sure if this effects you or not, but, with the full paths in there, it suggests that you want to work on closed workbooks.