Working around the "Close the workbook and INDIRECT breaks

[itsrich]

INDIRECT is great within the same workbook but use it to pull data from another workbook and all the formulas break.

• =XLOOKUP(B3,INDIRECT("'["&$L$1&".xlsm]"&L3&"'!$D:$D"),INDIRECT("'["&$L$1&".xlsm]"&L3&"'!$F:$F"),"Obsolete?")
• L1 = File Name
• L3 = Tab Name

How does one get around this? I need presitant values across workbooks.

I can use XLOOKUP without INDIRECT, but I am back to building a unique formula for every tab.

Grrrr.

 

[Fluff]

The Indirect function simply doesn't work with closed workbooks.

 

[itsrich]

I don't like that answer. It reminds me of other Microsoft Help I have found... Technically Correct, TOTALLY useless.

No disparagement to Fluff, just Microsoft.

Fluff, you helped me build a beautiful formula that I can use unless I have 7 workbooks open concurrently. My little laptop can not keep up!

Again, TY Fluff!

 

[itsrich]

To be clear, the only workaround is to use XLOOKUP without INDIRECT.

Is there any other formula that allows such easy construction of a formula?

Job security!

 

[Fluff]

If you want to put the file name & sheet name in a couple of cells & refer to them, indirect is the only option.

 

[itsrich]

While I have spun my wheels on this one, I have learned a great deal. TY to all!

 

[Fluff]

You're welcome & thanks for the feedback.