INDIRECT within XLOOKUP with maximum flexability

[itsrich]

This formula breaks when the file is closed. I know. I am open to other ideas.

S4 allows the change of the File Name
S3 allows me to change the Tab Name

The Goal
1. correct syntax to replace $D:$D with a cell reference of S5
2. correct syntax to replace $F:$F with a cell reference of S6

=XLOOKUP(B3,INDIRECT("'["&$S$4&".xlsm]"&S3&"'!S5"),INDIRECT("'["&$S$4&".xlsm]"&S3&"'!S6"),"Obsolete?")

XLOOKUP(B3,INDIRECT("'["&$S$4&".xlsm]"&S3&"'!$D:$D"),INDIRECT("'["&$S$4&".xlsm]"&S3&"'!$F:$F"),"Obsolete?") ~ this formula works

S3 = Tab Name = 20C-25-376

S4 = File Name = 2025 Lamination Calculator

S5 = Lookup Array = $D:$D

S6 = Return Array = $F:$F

 

[Tetra201]

You seem to have already asked the same question here.

 

[Fluff]

Duplicate post

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.