Cell Addressing

How would I go about using the value in one cell as part of a cell address or range of addresses? For example: cell a1 contains the value 20; and I want to use this value in this manner: sum(b"a1":b30). Or cell a1 contains 20 and cell a2 contains 35; sum("a1":"a2"). Or even a1=C and a2=10; then the cell address to be used in a different formula to use these two values as an address of c10. I've searched for hours and cannot find the answer, surely there must be some way to do this.

a b c
1 20
2 35
3
4 sum(a20:a35)

Hope this makes some kind of sense, I know what I want to do, just can't find the right way to word it. Thanks in advance,

Glen B.
[email protected].

You can use the INDIRECT() function in this manner to "piece" a reference together from string values and cell references.

=SUM(INDIRECT("A" & B1 & ":A" & B2)

Originally Posted by tigertiger

=SUM(INDIRECT(A1&"1:C"&A2))


.

Surely
=SUM(INDIRECT(A1 & "1:" & A1 & A2))

would be more apropriate?