Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
bpat1434
 
Posts: n/a
Default 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   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I link many cells to one particular cell? fish@divi Excel Discussion (Misc queries) 2 January 5th 05 12:00 AM
How do I link many cells to one particular cell? justinfishman22 Excel Discussion (Misc queries) 2 January 4th 05 01:09 AM
I want to format a cell based on an adjacent cells value Cumbo Excel Discussion (Misc queries) 1 December 13th 04 12:52 PM
Modify Row & Cell based on Multiple Data Brett Patterson Excel Worksheet Functions 0 November 5th 04 08:11 PM
Modify Row based upon cell value Brett Patterson Excel Worksheet Functions 0 November 5th 04 12:09 AM


All times are GMT +1. The time now is 04:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"