Repositioning a cell based on its value

[Paul987]

A rather complex (to me) problem:
I have a number that I want to move its position based on its value. The easiest way to think of it is stock prices. If the value increases one cent, I want the location of the number to move up one cell. If it decreases by two cents, I want the location to move down two cells. I would like to do this without any macros, as I need the flexibilty of formulas. For instance: cell A1 = 5331

Cell C50 = A1 when A1 = 5331, but if A1 changes to 5332, I want C49 = A1, and C50 = "". In this manner, the value could move up or down through column C.


Any one have any ideas? I will check back often to answer any questions.

Thanks for any and all help.

Paul

[Search33]

"Paul987" wrote:

>
> A rather complex (to me) problem:
> I have a number that I want to move its position based on its value.
> The easiest way to think of it is stock prices. If the value increases
> one cent, I want the location of the number to move up one cell. If it
> decreases by two cents, I want the location to move down two cells. I
> would like to do this without any macros, as I need the flexibilty of
> formulas. For instance: cell A1 = 5331
>
> Cell C50 = A1 when A1 = 5331, but if A1 changes to 5332, I want C49 =
> A1, and C50 = "". In this manner, the value could move up or down
> through column C.
>
>
> Any one have any ideas? I will check back often to answer any
> questions.
>
> Thanks for any and all help.
>
> Paul
>
>
> --
> Paul987
> ------------------------------------------------------------------------
> Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850
> View this thread: http://www.excelforum.com/showthread...hreadid=479777
>

You could set Column C to have all the same formula and set conditional
formatting so that it shows up in the cell you would like to see it in. This
however would be quite time consuming if there are too many rows.

OR

If there are a lot of columns: Make a helper column somewhere to the side
with the numbers that should be in each row. For example cell D50 would be
5331 and cell D49 woudl be 5332 (arrording to the numbers you gave)
then in C you can put the formula =if(D50=$A$1,$A$1,"") and this can be
copied down the column.

- Search

[RagDyeR]

Play with this to match your values:

=IF($A$1=ROW(A5000),$A$1,"")

Enter in Column C and copy down as needed.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


"Paul987" <[email protected]> wrote in
message news:[email protected]...

A rather complex (to me) problem:
I have a number that I want to move its position based on its value.
The easiest way to think of it is stock prices. If the value increases
one cent, I want the location of the number to move up one cell. If it
decreases by two cents, I want the location to move down two cells. I
would like to do this without any macros, as I need the flexibilty of
formulas. For instance: cell A1 = 5331

Cell C50 = A1 when A1 = 5331, but if A1 changes to 5332, I want C49 =
A1, and C50 = "". In this manner, the value could move up or down
through column C.


Any one have any ideas? I will check back often to answer any
questions.

Thanks for any and all help.

Paul


--
Paul987
------------------------------------------------------------------------
Paul987's Profile:
http://www.excelforum.com/member.php...o&userid=24850
View this thread: http://www.excelforum.com/showthread...hreadid=479777

[Paul987]

Thanks guys.

Both those solutions worked well, I guess it just a matter of not thinking too into it. One more question: how can I clear a cell based on an event, without a macro.

For instance if A1=B1, then C1=0? Ideas?

Thanks
Paul

[RagDyer]

What's in C1 now?

You can enter this in C1:

=IF(A1=B1,0,"Whatever you want in C1 when A1<>B1")

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Paul987" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks guys.
>
> Both those solutions worked well, I guess it just a matter of not
> thinking too into it. One more question: how can I clear a cell based
> on an event, without a macro.
>
> For instance if A1=B1, then C1=0? Ideas?
>
> Thanks
> Paul
>
>
> --
> Paul987
> ------------------------------------------------------------------------
> Paul987's Profile:
http://www.excelforum.com/member.php...o&userid=24850
> View this thread: http://www.excelforum.com/showthread...hreadid=479777
>