Remember Me?

#1
November 6th 04, 04:32 PM
 bpat1434 Posts: n/a
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

#2
November 6th 04, 05:17 PM
 Dave Peterson Posts: n/a

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

--

Dave Peterson

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post [email protected] Excel Discussion (Misc queries) 2 January 4th 05 11:00 PM justinfishman22 Excel Discussion (Misc queries) 2 January 4th 05 12:09 AM Cumbo Excel Discussion (Misc queries) 1 December 13th 04 11:52 AM Brett Patterson Excel Worksheet Functions 0 November 5th 04 07:11 PM Brett Patterson Excel Worksheet Functions 0 November 4th 04 11:09 PM

All times are GMT +1. The time now is 06:54 AM.