"#REF!" or other errors when linking two files

**au12010** · 06-07-2012, 06:22 PM

(Also posted at Mr. Excel: http://www.mrexcel.com/forum/showthread.php?t=640561)

I have two Excel files open. One contains a table of data. The columns represent months; the rows represent sales and expense figures for various regions and business units.

The other file contains formulas which link to the data in the first file. The formulas include VLOOKUP or HLOOKUP. For example, I might have a VLOOKUP intended to return the sales figure for a specific month in a specific region.

When both files are open, everything works fine; the formulas always return the values that they should.

However, at times (and the occurrences seem random; I cannot find a pattern), the following problem happens:

a. With both files open, I recalculate all formulas.
b. I then save and close both files.
c. At a later time, I re-open the file with the lookup formulas and do not refresh links (takes much too long; source file is large)
d. Instead of returning the correct values, the lookup formulas sometimes return either #REF! or simply a value of 0 when there should be some other number there.
e. As noted, this seems to happen at random; it does not happen every time I open the file.
f. Even more randomly, it does not seem to affect ALL the formulas. For example, I might have a column of identical formulas that vary only according to which region they are looking up the data for; sometimes, some of the formulas in the column work and some don't.
g. If I open the source file and recalculate, the problem goes away; the formulas then return the correct values.

I am (still) using Excel 2003.

I am totally baffled by this. Any suggestions much appreciated.

Thank you.

**vlady** · 06-07-2012, 07:15 PM

Welcome to the forum.

can you post the formula or better a dummy file..so others could take a look at it.
Thanks

**FDibbins** · 06-07-2012, 07:21 PM

this may or may not help your problem, but when i have had to reference a larger (data) file from a smaller (summary) file using lookups or whatever, and the larger 1 may be closed, i have actually PUT the summary in the larger file (lookups and all), and then just referenced the RESULTS of the summary from the summary file into the data file. it makes the link much simpler and quicker, as well as diagnosing any errors

give it a shot, what have you got to loose?

**au12010** · 06-07-2012, 07:48 PM

Thanks for your reply. I have attached 2 sample files. The "Source" file is a sample of the type of data that I want to reference with the "Report" file (although the actual source data is much larger: a range of about 200 colums x 3000 rows).

The "Report" file contains the lookup functions that link to the Source file (shaded cells contain the formulas). When both files are open, it all works perfectly. When "Source" is closed (which is preferable, because of its large size), the lookup formulas in "Report" often return #REF! or just 0 instead of the correct value.

Thanks again!

**au12010** · 06-07-2012, 07:57 PM

Ah, I see what you are saying - just a straight link to a cell in the source file, rather than a lookup function, might indeed solve the #Ref! error problem.

Unfortunately, I would then lose a lot of functionality. For example, my source file contains expense and revenue data for 8 business units, 7 geographic regions and 120 monthly periods. The "summary" report allows me to look up any desired combination of data (revenue and/or expenses for any region, any unit, any month) and change the parameters with just a keystroke or two, without having the massive source file open. Even better, I can send the summary report file to others so they can generate their own custom reports - they don't have to worry about the large size of the source file, and I don't have to worry about them opening the source file and corrupting my data.

Thanks so much for the suggestion. I do appreciate your feedback.

**FDibbins** · 06-07-2012, 08:10 PM

yes i do understand your predicament, i developed that technique for a similar requirement. and maybe thats what is causing some of your problems. certain functions just simply dont work on closed workbooks

maybe you could still just refernce the data needed into your summary file in hidden sheets, and still have the options you need?

is there perhaps a way that your could still use that approach, and in your data file "extract" all the data you might need, and in your "summary" file just options that will just reference the data that is asked for?

i had a file with 10 years worth of "waste" data, covering 50+ machines over 3 shifts, with 5 different types of "waste" the management summary (and data entry for that matter) file took over 30 minutes just to open, so i used that approach for the mngt summary and it opened in no time, and still gave them the functionality they needed. the summary file was read-only, so they could play with it but not mess with it