External Links result in #VALUE errors when external file is closed

[ziggyztz]

External references in my file result in #VALUE errors when both files aren't open. Excel 2010.

I have a file that someone else runs with productivity by sales person, site, and month. This information is displayed in a pivot table, but my formulas references the cells (not GETPIVOTDATA) and the file is in a folder that I only have read permissions to so I can't edit the file.

My file is in another folder. I've been trying to create external links in index/match/match formulas. I created named ranges and used OFFSET to make the data ranges dynamic. Index references a named range of all the data, match references a named range of the rows, and match reference a named range of the columns. Formulas work fine as long as the other file is open. When both are closed, I get #VALUE errors when reopening.

I've tried just using references to the cells with no OFFSET, moving the productivity file to a folder I have read/write permissions, and making the formulas array formulas.

From what I've found searching, it looks like there may be no solution and it's just how Excel works. So I'd just have to copy/paste the data into my file with no external links. Is that right? Or is there a solution I'm missing?

[6StringJazzer]

We have to see your actual formulas to understand what you're talking about. But some functions require the other workbook to be open, most notably INDIRECT but you didn't mention that one. I can't remember offhand what the others are. Is the named range in the file with the formulas, or defined in the external workbook?

There might be a way to do what you want, if you can explain in more detail exactly what you need to do. It would be ideal if you could attach both your file and the productivity file, although I imagine they would contain proprietary data. At a bare minimum you need to show your formulas.

[ziggyztz]

The formula was in this format with the first match matching a concatenated LocationName in column A and the second match matching "Month Year."

=INDEX('C:\Users\Me\Desktop\[Sales.xlsx]Sales Matrix'!$A$4:$Z$2000,MATCH(CONCATENATE(A$5,B$5),'C:\Users\Me\Desktop\[Sales.xlsx]Sales Matrix'!$A$4:$A$2000,0),MATCH(B$4,'C:\Users\Me\Desktop\[Sales.xlsx]Sales Matrix'!$A$4:$Z$4,0))

Named ranges were in my main file, but I tried the above without named ranges with the same result. The concatenated LocationName was in the production file and main file. I also tried putting the main file's concatenate outside of the formula and only referencing a cell. At this point, the report is only updated monthly so I think copy/paste the data is a low enough amount of work. Turning off automatic calculations also seems to work if I save with both files open.



I'm not familiar with Get & Transform. I'll look into it.

[drbacon]

Have you tried making a connection with the Get & Transform options? This would refresh the information on your workbook, so you don't have to depend on whether the workbook is open or not.