Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have two Excel files posted on SharePoint. With the master file, I've created INDEX, MATCH array formula, referencing the other file. I've entered the entire NUC, and the formula works fine while both files are open.

However, once the referenced report is closed, and Shift+Ctrl+Enter the cell, it returns #Ref! error. When I open the referenced report and do the same, it works fine.

Does anyone have any suggestions or recommendations for what I can do to fix it?

share|improve this question
What have you researched or tried so far to resolve this? Can you share the link syntax? – CharlieRB Mar 14 '14 at 16:04
I tried vlookup using NUC, it works fine. this issue only happens with index/match... – user2669043 Mar 14 '14 at 17:43
Here is the formula I am using NU is the file location. Again, the formula works fine as long as NUC\Filename.xlsx is open.... =INDEX('NUC\Filename.xlsx'!Table1[[A]:[Z]],MATCH(1,('NUC\Filename.xlsx'!Table1[F‌​orecastMonth]=[@ForecastMonth])*('NUC\Filename.xlsx'!Table1[Name]=[@Name]),0),11) – user2669043 Mar 14 '14 at 17:46
When you use NUC, do you mean UNC for the address on the server? – CharlieRB Mar 14 '14 at 18:18
sorry been playing too much with intel's new NUC:) I do mean UNC, and there is \\ before the address – user2669043 Mar 14 '14 at 18:53

According to this Microsoft Support document; Create an external reference (link) to a cell range in another workbook brackets need to be used around the file name when using external links to closed files (about half way down, 2nd external reference heading).

When the source is not open in Excel, the external reference includes the entire path.
External reference

So, in your formula, the Filename.xlsx should be [Filename.xlsx]

Also, I'm not sure, but it looks like your UNC should start with a \\ if there isn't a drive letter. But if it's working, don't fix it. :-)

share|improve this answer
Thanks, I've tried putting a square bracket, but when I do ctr-shft-enter, excel automatically removes square bracket, and will still return #Ref! error... – user2669043 Mar 14 '14 at 18:44

Names only work when referencing internal items. As soon as you want to reference an external item, the item must be open for excel to reference it by name. Once the workbook is closed, you'll need to reference actual cell ranges rather than the named range.

share|improve this answer
That actually worked! However, I'm now getting out of resource error... Does INDEX/MATCH require that much resources? – user2669043 Mar 17 '14 at 15:19
Depends on your array size and resources available – Raystafarian Mar 17 '14 at 15:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .