find a cell within a cell?

[Harry0]

for example if I say =sum(a$1:a$10) is their a way to say =sum(a1:a$(B2))? to have it be 10? That way to edit just b2 to change the length of the formula. Obviously that does not work but is their another alternative?

I was trying to use cell but that didnt work =sum(a1:cell("type",b2)
Hmmm
Thanks

 

[AlanSidman]

The only way I can think that this will work is with a VBA solution.

Option Explicit

Sub AddUP()
    Dim i As Long
    i = Range("B1").Value2
    Range("C1") = WorksheetFunction.Sum(Range("A1:A" & i))
End Sub

 

[Hui]

No VBA required

=Sum(Offset($A$1,,, $B$1,1))
or
=SUM(INDIRECT("$A$1:$A$" & $B$1))
or
=SUM($A$1:INDEX($A$1:$A$10, $B$1))

 

[pecoflyer]

And, as OFFSET and INDIRECT are volatile, Hui's third solution is the way to go and the syntax is easier too)