dynamic summed range based on a variable
 

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

dynamic summed range based on a variable
 

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?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Bruce" wrote in message
...
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

dynamic summed range based on a variable
 

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

"RagDyer" wrote:

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?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Bruce" wrote in message
...
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

dynamic summed range based on a variable
 

=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)


"Bruce" wrote in message
...
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

"RagDyer" wrote:

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?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Bruce" wrote in message
...
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