Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 at 16:04
    
I tried vlookup using NUC, it works fine. this issue only happens with index/match... –  user2669043 Mar 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 at 17:46
    
When you use NUC, do you mean UNC for the address on the server? –  CharlieRB Mar 14 at 18:18
1  
sorry been playing too much with intel's new NUC:) I do mean UNC, and there is \\ before the address –  user2669043 Mar 14 at 18:53

2 Answers 2

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
=SUM('C:\Reports[Budget.xlsx]Annual'!C10:C25)

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 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 at 15:19
    
Depends on your array size and resources available –  Raystafarian Mar 17 at 15:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.