Reference Row with Cell Value

[helpexcel]

Hi - I know how to do this with VBA, but is there a way to do it with a "regular" formula. So something like if C1 = 27 the Formula would be sum (A&C1:A100) which would sum A27 to A100.

 

[KOKOSEK]

=sum(indirect("a"&c1&":a100"))

 

[Fluff]

Like
=SUM(INDIRECT("A"&C1):A100)

 

[JLGWhiz]

hi - i know how to do this with vba, but is there a way to do it with a "regular" formula. So something like if c1 = 27 the formula would be sum (a&c1:a100) which would sum a27 to a100.

=if(c1=27,sum(a27:a100),"")

Not sure which way you mean?

 

[Rick Rothstein]

INDIRECT is Volatile... if you do it this way, the formula will be non-Volatile:

=SUM(INDEX(A:A,27):A100)

 

[Rick Rothstein]

INDIRECT is Volatile... if you do it this way, the formula will be non-Volatile:

=SUM(INDEX(A:A,27):A100)

That was supposed to have been...

=SUM(INDEX(A:A,C1):A100)