Links error if referenced file not opened
 

I have some references in a Main worksheet that link to another spreadsheet
just to populate it with data, no complicated formulas.

=LOOKUP(E2,'[NHP Animal Census Data File.xls]Clinical'!$A:$A,'[NHP Animal
Census Data File.xls]Clinical'!$C:$C)

Now when I open the Main worksheet the I get #REF! all the way down the
columns. Only if I open the other spreadsheet does the information populate.
I never had to open the other spreadsheet before now. It had been working
fine for months. Nothing has been changed as far as the formulas are
concerned. In fact I had a backup copy from a few weeks ago and the formulas
are exactly the same. Any ideas of what could have caused this and how I can
fix it?

Links error if referenced file not opened
 

Noticed no one had replied to this, so I thought I would offer a
longshot.

1) The most times I notice people getting a #REF is because they are
vlooking a item in a Pivottable in another spreadsheet (without that
sheet being open). There are workarounds, etc, for that, but you said
nothing has changed, so I don't know why that would be the problem. If
you are vlooking up in a Pivottable, email me privately and I'll give
you some tips.

2) Click tools-options-transition-Sheet options. Toggle these
settings. Sometimes I have seen sheets display certain errors when
these are enabled or disabled. Just a wild troubleshooting step;
probably won't help.

Sorry I couldn't offer any better advice, but I at least wanted to give
you one response :).


--
Lotus123
------------------------------------------------------------------------
Lotus123's Profile: http://www.excelforum.com/member.php...o&userid=28611
View this thread: http://www.excelforum.com/showthread...hreadid=526883

Links error if referenced file not opened
 

supergalaxygirl wrote...
I have some references in a Main worksheet that link to another spreadsheet
just to populate it with data, no complicated formulas.

=LOOKUP(E2,'[NHP Animal Census Data File.xls]Clinical'!$A:$A,
'[NHP Animal Census Data File.xls]Clinical'!$C:$C)

Now when I open the Main worksheet the I get #REF! all the way down the
columns. Only if I open the other spreadsheet does the information populate.
I never had to open the other spreadsheet before now. It had been working
fine for months. Nothing has been changed as far as the formulas are
concerned. In fact I had a backup copy from a few weeks ago and the formulas
are exactly the same. Any ideas of what could have caused this and how I can
fix it?

Don't reference entire columns. When the other workbook is open, this
may work because the external references would resolve to range
references. However, when the other workbook is closed, the external
references resolve to arrays, and arrays can be no larger than 65535
entries in any dimension. Full column references are just larger than
that.

So if your data only extended down to, say, row 10000, then change your
formula to

=LOOKUP(E2,'[NHP Animal Census Data File.xls]Clinical'!$A$1:$A$10000,
'[NHP Animal Census Data File.xls]Clinical'!$C$1:$C$10000)

Avoid using entire column references whenever possible. They may seem
like a good idea, but like merged cells they cause more trouble than
they're worth.