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

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?

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.