Dynamic VLOOKUP function

B

Barb Reinhardt

I have a VLOOKUP function that looks something like this:

=VLOOKUP(A1,[Book3]Sheet1!$A$1:$C$3,3,FALSE)

I want to be able to change the value of the workbook that's used for the
lookup function. Let's say the workbook name is stored in B1.

What do I need to do with the equation to get this to work?

Thanks in advance,
Barb Reinhardt
 
B

Biff

Hi!

You could use INDIRECT....however, this REQUIRES that the other wb be open.
As soon as the other wb is closed and the active wb calculates the formula
will return a #REF! error.

The formula would be:

=VLOOKUP(A1,INDIRECT("["&B1&"]Sheet1!A1:C3"),3,0)

Where B1 = File_Name.xls

Biff
 
B

Barb Reinhardt

I'll try that. Are there any other options where the WB can be closed?

Biff said:
Hi!

You could use INDIRECT....however, this REQUIRES that the other wb be
open. As soon as the other wb is closed and the active wb calculates the
formula will return a #REF! error.

The formula would be:

=VLOOKUP(A1,INDIRECT("["&B1&"]Sheet1!A1:C3"),3,0)

Where B1 = File_Name.xls

Biff

Reinhardt said:
I have a VLOOKUP function that looks something like this:

=VLOOKUP(A1,[Book3]Sheet1!$A$1:$C$3,3,FALSE)

I want to be able to change the value of the workbook that's used for the
lookup function. Let's say the workbook name is stored in B1.

What do I need to do with the equation to get this to work?

Thanks in advance,
Barb Reinhardt
Click to expand...
Click to expand...
 
B

Biff

Hi!

There is a VBA routine written by Harlan Grove that is supposed to do this.
I have never tried it but have seen it discussed in the forum. Try doing a
search for Harlan Grove Pull. Pull is the name of the routine.

Biff

Barb Reinhardt said:
I'll try that. Are there any other options where the WB can be closed?

Biff said:
Hi!

You could use INDIRECT....however, this REQUIRES that the other wb be
open. As soon as the other wb is closed and the active wb calculates the
formula will return a #REF! error.

The formula would be:

=VLOOKUP(A1,INDIRECT("["&B1&"]Sheet1!A1:C3"),3,0)

Where B1 = File_Name.xls

Biff

Reinhardt said:
I have a VLOOKUP function that looks something like this:

=VLOOKUP(A1,[Book3]Sheet1!$A$1:$C$3,3,FALSE)

I want to be able to change the value of the workbook that's used for
the lookup function. Let's say the workbook name is stored in B1.

What do I need to do with the equation to get this to work?

Thanks in advance,
Barb Reinhardt
Click to expand...
Click to expand...
Click to expand...
 
Top