Build a Cell Reference 1

[zdas04]

I know we've talked about this before, but I just can't find it in eng-tips or in Excel help.

Cell J12 = 498
Cell K12 = 559

I want cell L12 to be SUM(C498:C559)

Using simple concatenation (ampersand) I can get C498:C559 but when I put a SUM() around it I get #VALUE. I had high hopes for INDIRECT, but it just gave me the contents of C498, a colon, and the contents of C559.

I know this is easy, and I know I've seen it discussed here, but I just can't figure out what to call the functionality I need.

David

 

[GregLocock]

I think you are asking for something more complex than you think you are. The reason I say that is that you need to build the "C498:C559" string and evaluate it as a range.

AN alternative approach would be to use offset, I think.




Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[IDS]

=SUM(INDIRECT("C"&J12&":C"&K12))

Alternatively put ="C"&J12&":C"&K12 in L12 then
=SUM(INDIRECT(L12))



Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[GregLocock]

Good one. Yes reading between the lines in Help it does imply that is possible, without actually saying it.

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376

http://eng-tips.com/market.cfm?

 

[zdas04]

Doug,
That is exactly the step I was missing. Thank you.

David