dynamic summed range based on a variable

[Guest]

I want to sum a dynamic range based on a value (myValue). The idea behind
this is as myValue changes so will the range summed. The formula will first
get the column number of the start of the range, then add myValue and return
this as the end column reference in the sum formula.

For example,

Say myValue is the value of 2 in cell B2.
To Sum(A2:C3) I want to calculate C.
So Column(A2) + B2 = 3.

How can I convert this 3 back to a column reference and insert into the sum
formula?

Bruce

 

[RagDyer]

Would you care to restate your question.

You're using mixed references,
Column A2 ? ? ?
Calculate C - but you're looking for a column reference? or a row reference
in Column C?

 

[Guest]

Thanks RD, I thought I may have been a little misleading (i had a typo). I'll
try from another angle.

I want to adjust the summed range based on a value.

If myValue is 1 I want to sum(A2:A2).
If myValue is 3 I want to sum(A2:C2).
If myValue is 5 I want to sum(A2:E2).

Basically myValue adjusts the width (column's) within the summed range.

Bruce

 

[Peo Sjoblom]

=SUM(OFFSET($A$2,,,,MyValue))

or maybe better

=SUM(A2:INDEX(2:2,,MyValue))


the latter is not volatile thus won't recalculate whenever other cells are
changed and ask you if you want to
save although nothing has been changed

--
Regards,

Peo Sjoblom

(No private emails please)