vlookup table in external worksheet

[KarenF]

Hi,

I have a vlookup formula that references a range name in an external
workbook. When I have done this with earlier versions of Excel I don't
recall having any problems. However, now I keep losing my link to the lookup
table file, and my formula returns #REF! I always thought that with a
vlookup, it was not essential to have the lookup table file open. Maybe it
is.

Any ideas please?

Many thanks,

Karen.

 

[Dave Peterson]

If you open the other workbook, does the formula evaluate ok?

If no, then my guess is that your formula is wrong.

If yes, then my guess changes to...

You just upgraded to xl2002+ and answered no to the update links when the file
opened.

xl2002+ likes to recalculate any workbooks that were created in previous
versions. In earlier versions of excel, if you answer No to the update links
prompt, the existing values are kept. In xl2002+, you get errors.

Jim Rech posted a registry tweak:
http://groups.google.com/[email protected]

 

[Dave Peterson]

In fact, I'm changing my guess to the first one--you have a mistake in your
formula.

If I recall correctly, you'll get #value! errors, not #ref! errors with my
second guess.

 

[KarenF]

Hi Dave,

Hope you are well.

Thanks for your help - again! (Haven't tried the VBA to copy and paste on
file open yet but will let you know - solved the dependent data lists issue
though, so ta.)

Anyway, back to this. If I go to Edit, Links, and open source, or if the
file is already open, then the formulae work.

If I say yes to update links, and the lookup table file is not open, I need
to select Edit Links to re-establish the link (the error message in the
status area of the links box says "ERROR - undefined or non-rectangluar
name". The links don't find the source file automatically, yet they work
when the file is open.

I'll try the tweak and let you know how I get on.

Thanks again Dave.

Karen.

 

[Dave Peterson]

Don't forget to use that NameManager addin to see what that name points to.

(I don't have any other insight!)

 

[KarenF]

Thanks Dave. Will do.

K

Don't forget to use that NameManager addin to see what that name points to.

(I don't have any other insight!)