Indirect function with file path

4
votes

I am trying to create a formula that will look at four cells and return the contents of a cell defined by the contents of the 4 ref cells. for example:

a1 - file path

a2 - workbook name

a3 - sheet name

a4 - cell ref

I've tried to concantenate the cells to produce the full cell address to use in the indirect fucntion and also tried to 'build' the full address within the indirect function, either way I get a #ref!

I have tried to evaluate the formula and it looks fine as I step through building the file path but on the very last step goes to #ref!

Any advice that anyone can offer is greatly appreciated - Thanks

1
excelfilepath
INDIRECT will not get references from closed workbooks. So if the workbook "filePath" & "workbook name" is not open, then it will result in #REF. – Axel Richter
Thank You, I had wondered – Alex Facey

1 Answers

2
votes

Indirect() does not work with external files, unless they are open in the same instance of Excel.

A free add-in called "morefunc.xll", which you can download here, contains a function called Indirect.Ext which DOES work with external closed files.

This add-in does not work with 64 bit Excel, though, but if you are using 32bit Excel you will be able to use it.