I have a column of dates (I4:I30) with a column next to it for data that has
a new number in it weekly (J4:J30) for 26 weeks that is deducted from a
constant. I then have the result in J2. I can manually change the formula in
J2 weekly to have that weeks current result, but want to have it change
automatically when the weekly number is input in J4:J30. I'm sure there has
to be a way to do this.

Can you give me an example of the type of value in J4:J30 and the formula
used in J2 to help me better understand your challenge.

J

"Karen K" wrote:

I have a column of dates (I4:I30) with a column next to it for data that has
a new number in it weekly (J4:J30) for 26 weeks that is deducted from a
constant. I then have the result in J2. I can manually change the formula in
J2 weekly to have that weeks current result, but want to have it change
automatically when the weekly number is input in J4:J30. I'm sure there has
to be a way to do this.

The value in J4:J30 is "this weeks weight" (this particular week it is in
J13) and the formula in J2 is "starting weight"-"this weeks weight"
(starting weight is in M2, hidden). So, right now the formula in J2 is
=SUM(M2-J13). I have this "total loss result" plastered in a huge cell - big
& bold as a "pat on the back". I have just manually changed the "J" part of
it each week to show the current progress.

Thanks so much for trying to help!
Karen


"J" wrote in message
...
Can you give me an example of the type of value in J4:J30 and the formula
used in J2 to help me better understand your challenge.

J

"Karen K" wrote:

I have a column of dates (I4:I30) with a column next to it for data that
has
a new number in it weekly (J4:J30) for 26 weeks that is deducted from a
constant. I then have the result in J2. I can manually change the formula
in
J2 weekly to have that weeks current result, but want to have it change
automatically when the weekly number is input in J4:J30. I'm sure there
has
to be a way to do this.

If the weight loss is consistent, you could use the following formula:
=M2-(MIN(J4:J30))

This formula looks for the smallest value in the range of J4:J30 and
subtracts it from M2.

If this is not sufficient, I would consider using a dynamic range. I'm not
very good at this but I think that the dynamic range should allow you to
subtract the last cell (cell in the highest row number) from M2. A reference
to dynamic range information is he
http://www.ozgrid.com/Excel/DynamicRanges.htm

Hope this is of some help.

"Karen K" wrote:

The value in J4:J30 is "this weeks weight" (this particular week it is in
J13) and the formula in J2 is "starting weight"-"this weeks weight"
(starting weight is in M2, hidden). So, right now the formula in J2 is
=SUM(M2-J13). I have this "total loss result" plastered in a huge cell - big
& bold as a "pat on the back". I have just manually changed the "J" part of
it each week to show the current progress.

Thanks so much for trying to help!
Karen


"J" wrote in message
...
Can you give me an example of the type of value in J4:J30 and the formula
used in J2 to help me better understand your challenge.

J

"Karen K" wrote:

I have a column of dates (I4:I30) with a column next to it for data that
has
a new number in it weekly (J4:J30) for 26 weeks that is deducted from a
constant. I then have the result in J2. I can manually change the formula
in
J2 weekly to have that weeks current result, but want to have it change
automatically when the weekly number is input in J4:J30. I'm sure there
has
to be a way to do this.

Thanks so much, that will work (for now at least) & I'll look into the other
way just in case those values don't stay consistent!


"J" wrote in message
...
If the weight loss is consistent, you could use the following formula:
=M2-(MIN(J4:J30))

This formula looks for the smallest value in the range of J4:J30 and
subtracts it from M2.

If this is not sufficient, I would consider using a dynamic range. I'm not
very good at this but I think that the dynamic range should allow you to
subtract the last cell (cell in the highest row number) from M2. A
reference
to dynamic range information is he
http://www.ozgrid.com/Excel/DynamicRanges.htm

Hope this is of some help.

"Karen K" wrote:

The value in J4:J30 is "this weeks weight" (this particular week it is in
J13) and the formula in J2 is "starting weight"-"this weeks weight"
(starting weight is in M2, hidden). So, right now the formula in J2 is
=SUM(M2-J13). I have this "total loss result" plastered in a huge cell -
big
& bold as a "pat on the back". I have just manually changed the "J" part
of
it each week to show the current progress.

Thanks so much for trying to help!
Karen


"J" wrote in message
...
Can you give me an example of the type of value in J4:J30 and the
formula
used in J2 to help me better understand your challenge.

J

"Karen K" wrote:

I have a column of dates (I4:I30) with a column next to it for data
that
has
a new number in it weekly (J4:J30) for 26 weeks that is deducted from
a
constant. I then have the result in J2. I can manually change the
formula
in
J2 weekly to have that weeks current result, but want to have it
change
automatically when the weekly number is input in J4:J30. I'm sure
there
has
to be a way to do this.

Nevermind on my last post (even though I don't see it). I got a formula to
work! =M2-(Last_Cell_value(J4:J30))
Thanks to all anyway!


"Karen K" wrote in message
...
Thanks so much, that will work (for now at least) & I'll look into the
other way just in case those values don't stay consistent!


"J" wrote in message
...
If the weight loss is consistent, you could use the following formula:
=M2-(MIN(J4:J30))

This formula looks for the smallest value in the range of J4:J30 and
subtracts it from M2.

If this is not sufficient, I would consider using a dynamic range. I'm
not
very good at this but I think that the dynamic range should allow you to
subtract the last cell (cell in the highest row number) from M2. A
reference
to dynamic range information is he
http://www.ozgrid.com/Excel/DynamicRanges.htm

Hope this is of some help.

"Karen K" wrote:

The value in J4:J30 is "this weeks weight" (this particular week it is
in
J13) and the formula in J2 is "starting weight"-"this weeks weight"
(starting weight is in M2, hidden). So, right now the formula in J2 is
=SUM(M2-J13). I have this "total loss result" plastered in a huge cell -
big
& bold as a "pat on the back". I have just manually changed the "J" part
of
it each week to show the current progress.

Thanks so much for trying to help!
Karen


"J" wrote in message
...
Can you give me an example of the type of value in J4:J30 and the
formula
used in J2 to help me better understand your challenge.

J

"Karen K" wrote:

I have a column of dates (I4:I30) with a column next to it for data
that
has
a new number in it weekly (J4:J30) for 26 weeks that is deducted from
a
constant. I then have the result in J2. I can manually change the
formula
in
J2 weekly to have that weeks current result, but want to have it
change
automatically when the weekly number is input in J4:J30. I'm sure
there
has
to be a way to do this.