Array formula with indirect - can you spot what's wrong?

[Jimbob2000]

I'm trying to change an array formula (which works very nicely) that has a static reference to another worksheet (called "June 21") into one that uses a dynamic reference to the same worksheet using the indirect function. Cell A22 contains "June 21" as a string, so I tried substituting INDIRECT("'"&$A22&"'!$A:$A") in place of "'June 21'!$A:$A" in the formula. For some reason, when I put in the indirect functions, the formula doesn't work at all.

I'm including the old formula (that works) and the new formula (that doesn't). Can you see what's wrong with the new one?

{=SUM((LOOKUP('June 21'!$A$2:INDEX('June 21'!$A:$A,COUNTA('June 21'!$A:$A)),'Salary Info'!$A$2:INDEX('Salary Info'!$A:$A,COUNTA('Salary Info'!$A:$A)),'Salary Info'!$AI$2:INDEX('Salary Info'!$AI:$AI,COUNTA('Salary Info'!$A:$A))))*'June 21'!F$2:INDEX('June 21'!F:F,COUNTA('June 21'!$A:$A)))}

=SUM((LOOKUP(INDIRECT("'"&$A22&"'!$A$2:"&INDEX(INDIRECT("'"&$A22&"'!$A:$A"),COUNTA(INDIRECT("'"&$A22&"'!$A:$A")))),'Salary Info'!$A$2:INDEX('Salary Info'!$A:$A,COUNTA('Salary Info'!$A:$A)),'Salary Info'!$AI$2:INDEX('Salary Info'!$AI:$AI,COUNTA('Salary Info'!$A:$A)))*INDIRECT("'"&$A22&"'!F$2:"&INDEX(INDIRECT("'"&$A22&"'!F:F"),COUNTA(INDIRECT("'"&$A22&"'!$A:$A"))))))

 

[Jimbob2000]

Hey guys - Your suggestions were really helpful and I managed to get a version of this formula to work, but in the end, I found that you can't auto-fill using the formula, so I found a workaround to the problem. Thanks for your help!

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[jasonb75]

I found that you can't auto-fill using the formula

The formula should autofill the same as any other so I'm guessing that you mean that relative ranges don't change correctly?

For that to work, you either need to define the ranges using the address function within indirect, or use R1C1 references (R1C1 is the easier method if you understand it), to provide the correct syntax I would need the version of the formula that worked and would need to know which cell that formula will be entered into before you drag / fill the sheet with it.