Automatic link updates had to be disabled because the file will lock up some
machines that just can't handle updating 2700 links. That wasn't a problem
until I added a record to one of the source files (while the workbook with
the links is closed) and the next time we open the workbook, when vlookup
attempts to retrieve values from that record it returns #N/A until the link
is updated.

Any idea why vlookup won't read newly entered data from a source file that
was edited before the workbook containing the lookup value was opened?

Every other record in the source will display it's lookup values just
correctly and accurately.

It sounds like you hit an internal excel limit.

I think that in xl2k, I changed a few =vlookup() formulas from looking at whole
columns to a specific range to avoid that too many links message.

I don't have a workaround for you -- other than opening that other workbook.

mwgrutter wrote:

Automatic link updates had to be disabled because the file will lock up some
machines that just can't handle updating 2700 links. That wasn't a problem
until I added a record to one of the source files (while the workbook with
the links is closed) and the next time we open the workbook, when vlookup
attempts to retrieve values from that record it returns #N/A until the link
is updated.

Any idea why vlookup won't read newly entered data from a source file that
was edited before the workbook containing the lookup value was opened?

Every other record in the source will display it's lookup values just
correctly and accurately.

--

Dave Peterson

Thank you for the responce Dave, but that doesn't seem to be the problem. I
understand the lag in updating that many links to a 20 Column, 4000 row
source file, but that's why we disabled the automatic link updates.
The problem is, when you enter information into a cell that is used for the
LOOKUP_VALUE in the VLOOKUP function, the value returned from the source file
is the value prior to the source file being edited and saved. If the source
file is opened prior to entering the information for the VLOOKUP, everything
works just fine. If the source file is closed (and has been saved with the
new data) the previous source data is entered instead of the current data.
And it is not only happening with large source files and large arrays. A two
column, 500 row array with only one VLOOKUP link causes the same problem.

Thanks anyway.

"Dave Peterson" wrote:

It sounds like you hit an internal excel limit.

I think that in xl2k, I changed a few =vlookup() formulas from looking at whole
columns to a specific range to avoid that too many links message.

I don't have a workaround for you -- other than opening that other workbook.

mwgrutter wrote:

Automatic link updates had to be disabled because the file will lock up some
machines that just can't handle updating 2700 links. That wasn't a problem
until I added a record to one of the source files (while the workbook with
the links is closed) and the next time we open the workbook, when vlookup
attempts to retrieve values from that record it returns #N/A until the link
is updated.

Any idea why vlookup won't read newly entered data from a source file that
was edited before the workbook containing the lookup value was opened?

Every other record in the source will display it's lookup values just
correctly and accurately.

--

Dave Peterson

Are you saying that the only #n/a's you get back are from the =vlookup()
formulas that point to closed books?

If that's the case, then I did misunderstand the original question.

Jim Rech posted a registry tweak:
http://groups.google.com/groups?thre...GP11.phx .gbl

mwgrutter wrote:

Thank you for the responce Dave, but that doesn't seem to be the problem. I
understand the lag in updating that many links to a 20 Column, 4000 row
source file, but that's why we disabled the automatic link updates.
The problem is, when you enter information into a cell that is used for the
LOOKUP_VALUE in the VLOOKUP function, the value returned from the source file
is the value prior to the source file being edited and saved. If the source
file is opened prior to entering the information for the VLOOKUP, everything
works just fine. If the source file is closed (and has been saved with the
new data) the previous source data is entered instead of the current data.
And it is not only happening with large source files and large arrays. A two
column, 500 row array with only one VLOOKUP link causes the same problem.

Thanks anyway.

"Dave Peterson" wrote:

It sounds like you hit an internal excel limit.

I think that in xl2k, I changed a few =vlookup() formulas from looking at whole
columns to a specific range to avoid that too many links message.

I don't have a workaround for you -- other than opening that other workbook.

mwgrutter wrote:

Automatic link updates had to be disabled because the file will lock up some
machines that just can't handle updating 2700 links. That wasn't a problem
until I added a record to one of the source files (while the workbook with
the links is closed) and the next time we open the workbook, when vlookup
attempts to retrieve values from that record it returns #N/A until the link
is updated.

Any idea why vlookup won't read newly entered data from a source file that
was edited before the workbook containing the lookup value was opened?

Every other record in the source will display it's lookup values just
correctly and accurately.

--

Dave Peterson


--

Dave Peterson