Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Repositioning a cell based on its value
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 |
#2
|
|||
|
|||
Repositioning a cell based on its value
"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 |
#3
|
|||
|
|||
Repositioning a cell based on its value
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" wrote in message ... 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 |
#4
|
|||
|
|||
Repositioning a cell based on its value
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 |
#5
|
|||
|
|||
Repositioning a cell based on its value
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatiing based on another cell | Excel Discussion (Misc queries) | |||
how to set up one cell that assigns a value based on the value of another cell | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions | |||
up to 7 functions? | Excel Worksheet Functions | |||
make a cell empty based on condition | Charts and Charting in Excel |