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"))))))

 

[jasonb75]

Best guess, the workbook referred to in A22 is not open.

If you're dragging the formula down to say A33 referring to 12 workbooks (1 per month) then all 12 will need to be open.

 

[Jimbob2000]

Best guess, the workbook referred to in A22 is not open.

If you're dragging the formula down to say A33 referring to 12 workbooks (1 per month) then all 12 will need to be open.

Thanks Jason - the sheet referred to in A22 is a sheet in the same workbook, so it is open

 

[Fluff]

Looks like you're missing a closing bracket
=SUM((LOOKUP(INDIRECT("'"&$A22&"'!$A$2"):INDEX(INDIRECT("

 

[Jimbob2000]

Looks like you're missing a closing bracket
=SUM((LOOKUP(INDIRECT("'"&$A22&"'!$A$2"):INDEX(INDIRECT("

Hi there -- thanks for the reply! The bracket is closed, but not until later in the formula. I put the bracket here because I was thinking that the part underlined and in blue was part of the range that's started with first INDIRECT(... but that might be totally wrong.

=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"))))))

I tried moving the bracket as you suggested, and I get an #N/A error, where I was getting a #REF error...

=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")))))

The #N/A error seems more familiar and friendly, but I'm still to quite sure what's not working...

 

[jasonb75]

What is in A22?

If it is a date then you probably need to use the text function to format it correctly.

 

[Jimbob2000]

In A22 is "June 21" formatted as text.

Something else that's a bit odd is that it looks as though if I use a static reference in place of the more complex Indirects, it still doesn't work.

=SUM((LOOKUP('June 21'!$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'!$AL$2:INDEX('Salary Info'!$AL:$AL,COUNTA('Salary Info'!$A:$A)))*'June 21'!$A$2:INDEX(INDIRECT("'"&$A22&"'!F:F"),COUNTA(INDIRECT("'"&$A22&"'!$A:$A")))))

 

[jasonb75]

Ignore this, realised what I was missing.

 

[jasonb75]

Think this is correct

{=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")))))}

Correct syntax is INDIRECT(...):INDEX(INDIRECT(...)) not INDIRECT(...&INDEX(INDIRECT(...))) (hope that makes sense).

 

[Fluff]

I tried moving the bracket as you suggested, and I get an #N/A error, where I was getting a #REF error...

That suggests the indirect is working, but the lookup can't find something.