Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
add comment

3 Answers

up vote 4 down vote accepted

This can be achieved with a very elegant formula:

=SUM($S$2:INDIRECT($S$1&ROW()))
share|improve this answer
    
+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
add comment

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)

share|improve this answer
    
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
add comment

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!!!

share|improve this answer
    
Yes, that last formula should work - is that what you're saying? –  barry houdini Feb 22 '13 at 19:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.