Using Indirect

[HowieG53]

I have this formula in a cell (H12) on a summary sheet

=SUMIF('[TN-53 Book Sheets.xlsx]01450.01'!$A$10:$A$70,"<="&$H$3,'[TN-53 Book Sheets.xlsx]01450.01'!$E$10:$E$70)

The value 01450.01 (used twice in the formula) is a tab name in the source file and it's the value in cell A12 of the sheet.
When I copy the formula to the next row I want to pick up the value in A13..etc.

I figure an INDIRECT reference to A12 (A13 etc) is what's needed, but I can't quite hit the syntax.

TIA for any help.
Howie

 

[jasonb75]

Welcome to MrExcel

It looks like you need

=SUMIF(INDIRECT("'[TN-53 Book Sheets.xlsx]"&A12&"'!$A$10:$A$70"),"<="&$H$3,INDIRECT("'[TN-53 Book Sheets.xlsx]"&A12&"'!$E$10:$E$70"))

 

[HowieG53]

Welcome to MrExcel

It looks like you need

=SUMIF(INDIRECT("'[TN-53 Book Sheets.xlsx]"&A12&"'!$A$10:$A$70"),"<="&$H$3,INDIRECT("'[TN-53 Book Sheets.xlsx]"&A12&"'!$E$10:$E$70"))

I had tried similar, I was leaving out the ") after the $A$70 (not closing the first INDIRECT), so when I got to the second INDIRECT excel didn't offer options. that should have been my clue.

Thanks very much, you've saved me a lot of time.
Howie