# Excel: how can I use a cell value as part of a range?

I'm working with a large Excel file on a monthly basis. In this file I have to find something like a sum as in

=sum(S2:AC2)


The problem is that the column of the last cell in the range changes. I would like to be able to do something like this:

In a say cell S1 I enter a value AC and then later I can write

=sum(S2:value(S1)2)


and have this have the same effect as though I had written

=sum(S2:AC2)


I know this can be done. I've seen it a very long time ago. It would be a huge time saver. I have hundreds of lines of the for =sum(S2:AC2). But next month they may all have to change to =sum(S2:BF2). To have one place to change the end of the range would be nice.

Incidentally, the row number actually changes too, but in an entirely predictable manner.

Ideally I would like to be able to concatenate the name such as:

=sum(S2: & S1 & row() )


where I'm using row() to get the current row number

-

This can be achieved with a very elegant formula:

=SUM($S$2:INDIRECT($S$1&ROW()))

-
+1 Peter - I was missing that trick....... –  barry houdini Feb 22 '13 at 21:30
it's a nice trick, esp. when trying to avoid volatility in dynamically named ranges! Instead of the volatile =OFFSET($A$1,0,0,rows,cols), =$A$1:INDEX($A:$Z,rows,cols) will produce the same result, but is non-volatile! :-) –  Peter Albert Feb 22 '13 at 21:37

One way is to use INDIRECT like this

=SUM(INDIRECT("S2:"&S1&"2"))

....but that means "hardcoding" the row number....

If you don't have gaps in the data it might be better to define a "dynamic named range" based on the number of values in the row, e.g. define a name Range based on this formula

=OFFSET($S$2,0,0,1,COUNTA($S$2:$IV$2))

then you can just use

=SUM(Range)

-
I can't have it hardcode the row number. That needs to change. –  A.Ellett Feb 22 '13 at 19:13
Is there a reason you can't just sum a larger range, e.g. =SUM(S2:IV2)? - any blanks in there will just be ignored. Will there be the same number of values in each row? –  barry houdini Feb 22 '13 at 19:21
Yes. There is other stuff happening in the later columns. It's a bad project design, but I'm stuck with it. –  A.Ellett Feb 22 '13 at 19:28

I've managed something of a work around. But, I'm not happy with it.

Let's say that in cells:

 cell(AB1) = "AD"
cell(AC1) = "AG"


then in row x I have the following

 cell(ABx)=AB$1 & row() cell(ACx)=AC$1 & row()


and then I can set

 cell(AAx)=sum(indirect(ABx &":"& ACx))


While this works, it means I've got to manually create two columns with the desired code.

It would be nice to be able to do something like

 cell(AAx)=sum(indirect(AB$1 & row() &":"& AC$1 & row()))


Oh, and it does work!!!

-
Yes, that last formula should work - is that what you're saying? –  barry houdini Feb 22 '13 at 19:46