vlookup table in external worksheet

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.

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/groups?thre...TNGP11.phx.gbl

KarenF wrote:
>
> 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

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 wrote:
>
> 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

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" wrote:

> 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 wrote:
> >
> > 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
>

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

(I don't have any other insight!)

KarenF wrote:
>
> 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" wrote:
>
> > 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 wrote:
> > >
> > > 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
> >

--

Dave Peterson

Thanks Dave. Will do.

K

"Dave Peterson" wrote:

> Don't forget to use that NameManager addin to see what that name points to.
>
> (I don't have any other insight!)
>
> KarenF wrote:
> >
> > 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" wrote:
> >
> > > 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 wrote:
> > > >
> > > > 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
> > >
>
> --
>
> Dave Peterson
>