Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Modify Row & Cell Contents based upon Cells Values
Hello all. What I'm Looking to do is the following: -- Retrieve the date from cell $B2. -- Read the value in $K2. If $B2's date is older than 14 regular days, not just business, AND the value of $K2 is "Awaiting Pickup" then I want the row to change to a red color, and the value of $A2 to change to: "[!]". So I thought I could do it with an =IF(AND(), $A2='[!]', $A2='') statement. What I tried to use was the following: Code: -------------------- =IF(AND((TODAY()-14)DATEVALUE($B2), $K2="Awaiting Pickup"), $A2="[!]", $A2="") -------------------- I figured that would have worked since the function help that MS gives is: =IF(*test_statement*, *value_if_true*, value_if_false) Where did I go wrong, and how do I fix it? Any help would be greatly appreciated. Brett Patterson -- bpat1434 ------------------------------------------------------------------------ bpat1434's Profile: http://www.excelforum.com/member.php...o&userid=16155 View this thread: http://www.excelforum.com/showthread...hreadid=275903 |
#2
|
|||
|
|||
First, a formula in one cell can't change the value in another cell.
You could use a formula like that directly in A2 if you wanted: =IF(AND((TODAY()-14)$B2,$K2="Awaiting Pickup"),"[!]","") I'm assuming that $B2 actually contains a date. =Datevalue() is nice when you have text in that cell--not when it's a real dated. And to show that row in red, you could select row 2 (with A2 active) and use this formula in the format|conditional formatting "formula is" box: =$A2="[!]" If you can't use that long formula in A2, then you could mimic that long formula in your conditional formatting "formula is" box: =AND((TODAY()-14)$B2,$K2="Awaiting Pickup") bpat1434 wrote: Hello all. What I'm Looking to do is the following: -- Retrieve the date from cell $B2. -- Read the value in $K2. If $B2's date is older than 14 regular days, not just business, AND the value of $K2 is "Awaiting Pickup" then I want the row to change to a red color, and the value of $A2 to change to: "[!]". So I thought I could do it with an =IF(AND(), $A2='[!]', $A2='') statement. What I tried to use was the following: Code: -------------------- =IF(AND((TODAY()-14)DATEVALUE($B2), $K2="Awaiting Pickup"), $A2="[!]", $A2="") -------------------- I figured that would have worked since the function help that MS gives is: =IF(*test_statement*, *value_if_true*, value_if_false) Where did I go wrong, and how do I fix it? Any help would be greatly appreciated. Brett Patterson -- bpat1434 ------------------------------------------------------------------------ bpat1434's Profile: http://www.excelforum.com/member.php...o&userid=16155 View this thread: http://www.excelforum.com/showthread...hreadid=275903 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I link many cells to one particular cell? | Excel Discussion (Misc queries) | |||
How do I link many cells to one particular cell? | Excel Discussion (Misc queries) | |||
I want to format a cell based on an adjacent cells value | Excel Discussion (Misc queries) | |||
Modify Row & Cell based on Multiple Data | Excel Worksheet Functions | |||
Modify Row based upon cell value | Excel Worksheet Functions |