Excel Indirect function to external workbook - REF! result

Question

I was writing a function to link indirectly to an external workbook and I came up with this:

=+INDIRECT("=+'"&$W$2&"\"&$Y$4&"\"&$X$4&"\["&$W$4&"]"&MID($W$4,1,LEN($W$4)-4)&"'!"&$U$15)

That though returns #REF! as result

The part within the () brackets, when evaluated return the following: "=+'C:\Folder0\Folder1\Folder2\Folder3\Folder4\Folder5\Folder6\Folder7\Folder8\ [MyFile.csv]NameTab'!$D$2"

and if I were to use it directly in a cell (once evaluated) would work

Could someone help me understand what is wrong here?

`INDIRECT` function is designed to work with open workbooks only. — shrivallabha.redij, May 31 '18 at 10:11
Possible duplicate of [Referencing value in a closed Excel workbook using INDIRECT?](https://stackoverflow.com/questions/28461672/referencing-value-in-a-closed-excel-workbook-using-indirect) — Luuklag, May 31 '18 at 11:12

score 1 · Answer 1 · answered May 31 '18 at 11:13

As noted in the comments above, Indirect() referencing external workbooks only works when the external workbook is open, which defeats the purpose.

The morefunc.xll (google it) add-in has a function called Indirect.ext, which works with closed external workbooks, but there are some limitations (i.e. it does not work in newer versions of Excel).

This post in XtremeVBTalk claims it has a better approach.

Excel Indirect function to external workbook - REF! result

1 Answers1

Linked