# 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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### 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!

#### Eric W

##### MrExcel MVP

Replies
5
Views
434
Replies
3
Views
160
Replies
1
Views
360
Replies
4
Views
197
Replies
10
Views
199

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,713
Messages
5,833,269
Members
430,200
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back