Vlookup issues when source data is in another workbook?

[Kathy L.]

Hello - are there limitations around having the 'Table Array' in the vlookup
formula be another workbook? In other words, does the other workbook have to
be open to work, or any other limitation? I ask, because I'm trying to have
a vlookup to another workbook, and do not want users to have to open this
other workbook. When they open the file with the vlookup formula, we are
getting the prompt asking if they want to Update the data. When I select
Update, it doesn't seem to work. It seems to only show the values from the
vlookup from the last time I saved the file, or works if I open the other
workbook with the destination data. I hope that makes sense. Thank you.

 

[Sheeloo]

I tested with Excel 2007...

The value got update even when the source file was closed.

I had to enable 'Automatic update of links'....

 

[Kathy L.]

Thank you...unfortunately I'm on Excel 2003, sorry I should have mentioned.
I do have the automatic update of links enabled in the options.

 

[Sheeloo]

As far as I know, VLOOKUP works with closed file also...

INDIRECT requires the other file to be open.

 

[T. Valko]

What's your formula look like:

When the source file is closed Excel "complains" if the table_array is more
than 16375 rows. If your using something like this:

=VLOOKUP(A1,'C:\TV\[xxx.xls]Sheet1'!A:B,2,0)

Use a smaller specific range in the table_array if you can.

 

[Kathy L.]

Thank you...good tip. My formula did look like this, but now I added in the
specific rows (2 through 1260) in the table_array. Unfortunately, this
morning, it seems the Update still isn't pulling as I'd expect. Refreshes
fine once I open the data file, but when I first open and select Update Data,
I still get the message that says "This workbook contains one or more links
that cannot be udpated."

What's your formula look like:

When the source file is closed Excel "complains" if the table_array is more
than 16375 rows. If your using something like this:

=VLOOKUP(A1,'C:\TV\[xxx.xls]Sheet1'!A:B,2,0)

Use a smaller specific range in the table_array if you can.

--
Biff
Microsoft Excel MVP

Hello - are there limitations around having the 'Table Array' in the
vlookup
formula be another workbook? In other words, does the other workbook have
to
be open to work, or any other limitation? I ask, because I'm trying to
have
a vlookup to another workbook, and do not want users to have to open this
other workbook. When they open the file with the vlookup formula, we are
getting the prompt asking if they want to Update the data. When I select
Update, it doesn't seem to work. It seems to only show the values from
the
vlookup from the last time I saved the file, or works if I open the other
workbook with the destination data. I hope that makes sense. Thank you.