Indirect with variable

volkl77

Board Regular
Joined
Apr 5, 2008
Messages
76
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
Joined
Aug 18, 2015
Messages
12,196
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.
 
Master Excel Bundle

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.

Forum statistics

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

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top