CODEWITHSUNDEEP
excelexcel-formula
M.Cio
M.Cio

Reputation: 1

Excel Indirect function to external workbook - REF! result

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?

Upvotes: 0

Views: 4864

Answers (1)

teylyn
teylyn

Reputation: 35900

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.

Upvotes: 1

Related Questions