# 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