formula with in cell reference

[Lamb Chop]

A1 =5 B1=3
A2 =65
A3=12
....
....


How can I do this:


I want C1 have formula to do this:

the sum of A1 to A3

but if B1 change to 5 then

C1 = sum(A1:A5)

and when B1 change to 100
C1=sum(a1:A100)


ie. change the reference formula according to B1.

I have tried C1=sum(a1:A(B1))

but it does not work.

 

[Carim]

Hi,

Use :
=SUM(A1:OFFSET(A1,B1,0))

HTH

 

[Lamb Chop]

Thanks Carim. You are very helpful.