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.