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?

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 .

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.