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

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" wrote in message
...
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

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

"Biff" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
I'll try that. Are there any other options where the WB can be closed?

"Biff" wrote in message
...
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" wrote in message
...
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