# Indirect with variable

#### volkl77

##### Board Regular
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.

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### Eric W

##### MrExcel MVP
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

##### Board Regular
Perfect, thank you!

Replies
7
Views
72
Replies
2
Views
120
Replies
1
Views
76
Replies
5
Views
40
Replies
9
Views
151