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.
 

Some videos you may like

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
Joined
Aug 18, 2015
Messages
10,219
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,040
Messages
5,526,409
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top