On Sheet2 I am trying to sum the values in a row of a range on Sheet1 over a dynamic range of columns based on a lookup value for the from column and a fixed variable for how many columns to right of that lookup value for the to column.

I am using INDEX-MATCH to find the from cell based on certain reference, INDEX-MATCH with a reference added to the column lookup to find the to column, and CELL to get the position rather than value of the results.

What I have is the following:

=SUM(
CELL("address",
INDEX(Sheet1!$B$4:$BA$36,MATCH($A$1,Sheet1!$A$4:$A$36),MATCH(D$3,Sheet1!$B$3:$BA$3,0)))
&":"&
CELL("address",
INDEX(Sheet1!$B$4:$BA$36,MATCH($A$1,Sheet1!$A$4:$A$36),MATCH(D$3,Sheet1!$B$3:$BA$3,0) 'Control Panel'!$C$2)))

Control Panel!$C$2 is my variable for how many columns to the right of the from column I want the to column to be.

Obviously, this is not working. I suspect it's because the concatenated text in the SUM() reference the full file name rather than 'Sheet1'![from]:[to]. Not sure if this is the case, but also can't figure out how to get just the A1 cell position for the to.

Any ideas how I can get this to work?

CodePudding user response：

Figured it out:

=SUM(INDEX(Sheet1!$B$4:$BA$36,MATCH($A$1,Sheet1!$A$4:$A$36),MATCH(D$3,Sheet1!$B$3:$BA$3,0))):INDEX(Sheet1!$B$4:$BA$36,MATCH($A$1,Sheet1!$A$4:$A$36),MATCH(D$3,Sheet1!$B$3:$BA$3,0) 'Control Panel'!$C$2))

Literally just needed a colon between the two index-matches. Still not sure why this works, as the index-match should return a value.

CodePudding user response：

I am trying to sum the values in a row of a range on Sheet1 over a dynamic range of columns based on a lookup value for the from column and a fixed variable for how many columns to right of that lookup value for the to column.

I am using INDEX-MATCH to find the from cell based on certain reference, INDEX-MATCH with a reference added to the column lookup to find the to column, and CELL to get the position rather than value of the results.