Indirect with variable

[volkl77]

I am using the formula below and it works great.

SUMPRODUCT(SUMIF(INDIRECT("'"&REF!$C$2:$C$26&"'!$B$10:$V$10"),K$1,INDIRECT("'"&REF!$C$2:$C$26&"'!$B$17:$V$17")))

My only challenge is that some of the tabs in the range $C$2:$C$26 don't exist yet. So to get around this I have just adjusted the formula based on the tabs that exist in the range eg. $C$2:$C$18 instead.

Since I won't be the one using the spreadsheet I am hoping to change $C$26 to a reference to cell D1 that has a formula which returns the last cell with a tab that exists. so in this case, D1 = $C$18.

I have tried numerous variations but I can't seem to get anything to work. I am not even sure if it is possible to be honest. Any help would be appreciated.

Thanks.

 

[Eric W]

Here are a couple of options. Try:

=SUMPRODUCT(IFERROR(SUMIF(INDIRECT("'"&Ref!$C$2:$C$26&"'!$B$10:$V$10"),K$1,INDIRECT("'"&Ref!$C$2:$C$26&"'!$B$17:$V$17")),0))

and confirm it by pressing Control+Shift+Enter. This will ignore empty cells in C2:C26, as well as any populated cells with an invalid sheet name.


Option 2 you can use a dynamic named range. Go to the Formulas tab, click Name Manager > New > and enter MySheets for the name and

=OFFSET(Ref!$C$2,0,0,COUNTA(Ref!$C$2:$C$26))

for the Refers to:.

You can now change your formula to:

=SUMPRODUCT(SUMIF(INDIRECT("'"&MySheets&"'!$B$10:$V$10"),K$1,INDIRECT("'"&MySheets&"'!$B$17:$V$17")))

and whenever you add a sheet name to the end of the list, the formula will automatically include it.

 

[volkl77]

Perfect, thank you!

 

[Eric W]

Glad to help!