INDIRECT and External workbooks

[Snypa]

Hello,

I was working on a formula and when I released INDIRECT does not work when workbooks are closed. The reason I need to use indirect is because I do not have control over the source workbook and sometimes columns are added/removed.

Here is a section on my formula:

=INDIRECT("'\\fileserv\scanning\[STORES.xlsx]Stores List'"&"!"&SUBSTITUTE(ADDRESS(1,Settings!F$27,4),"1","") & ROW()-1)

The column I was is stored in Settings!F$27. Is there any way I can make this work without needing to have the Stores file open?

 

[JustynaMK]

Hi Snypa,

I suggest that you review this thread:

https://www.mrexcel.com/forum/excel-questions/1080504-how-reference-cells-another-workbook-based-path-value-cell.html

+ Microsoft's INDIRECT function webpage states the same:
If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF ! error value.

https://support.office.com/en-us/article/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261