INDIRECT for filename. Indirect for Cell Reference at the same time?

[itsrich]

I am open to, "here is a better way..."

This formula works: =XLOOKUP(A87,INDIRECT("'"&$P$99&"'!$3:$3"),INDIRECT("'"&$P$99&"'!$19:$19"))

P99 = [2025 Lamination Calculator.xlsx]Totals
A87 = 1000 - 18x69
P100 = $19:$19
P101 = $3:$3

I want a cell reference for $3:$3 & $19:$19.

While I use INDIRCT, the syntax/formating for this formula is daunting. I have not found a rule for " or '. I ask for the help of the NON-A.I. Consortium.

 

[Tetra201]

Try

=XLOOKUP(A87,INDIRECT("'"&$P$99&"'!"&$P$101),INDIRECT("'"&$P$99&"'!"&$P$100))

 

[itsrich]

#REF = Error

This does not work =XLOOKUP(A87,INDIRECT("'"&$P$99&"'!"&$P$101),INDIRECT("'"&$P$99&"'!"&$P$100))

 

[Tetra201]

The formula offered in Post #2 has been tested to work as intended.

You must keep "2025 Lamination Calculator.xlsx" open for the formula to work -- please read help for the INDIRECT function.

 

[itsrich]

So this is a cleaner restatement of the formula I am trying to develop. Shout out to all the individuals on this who helped develop this formula!! Fluff, Jan Karel, Tetr201 and more. Thank you for getting me this far.

I am at the point where I have tried all the itererations of syntax variations. Help me figure out the correct combination of " and ' and & to make the formula work.

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

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

Again, thank you for your help