Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Creating cells that draw attention .

    In the attached worksheet, I want to keep track of guests' property that leaves it in the facility after they had checked out. We will send out three (3) notice letters every thirty (30) days from departure date. I want to show the incremental 30 day dates in columns L, N, P. I want these dates to be blank if the columns J and K are completed. If Columns J and K are not completed ( filled out) and the current date is past any one of the NOTICE DATES, and the Notice Mailed Date ( Columns M, O and Q) are not completed, then the 30, 60, 90 Day Notice Dates cells (Columns L, N and P) are conditionally formatted and filled with RED.

    Is this too much for a cell to do?

    Thanks
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    MNN,

    This can be accomplished via formula and conditional formatting. That said, however, if you want to keep the history, e.g. first 30 day notice mailed and then the property is subsequently picked up do you want the 30 day notice date to still show or be blanked? The process then becomes one for macros if you want to preserve the history. Of course, Steve or Zeddy may prove me wrong on this!

    Something like this?
    MNN.JPG
    Last edited by RetiredGeek; 2014-03-23 at 12:44.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Yes if the property is picked up after the 90 day letter, then I would want the history to remain that the notification letters went out. If the property was picked up before the 30 day notification went out then the 30, 60 & 90 day notice dates would be erased.

    The formula is getting complicated. Can anyone assist with this

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    MNN,

    I think these formulas along with conditional formatting will do the trick.

    L7: =IF(AND($G7<>"",$F7<>"",OR($K7="",$K7>$F7+30)),$F7 +30,"")
    N7: =IF(AND($G7<>"",$F7<>"",OR($K7="",$K7>$F7+60)),$F7 +60,"")
    P7: =IF(AND($G7<>"",$F7<>"",OR($K7="",$K7>$F7+90)),$F7 +90,"")

    Once entered in the indicated cells they can be filled down.
    Note: you'll get the little triangles in the upper left corner of the cells indicating that the formula refers to empty cells, you can tell Excel to ignore this error.
    MNN.JPG
    Conditional formatting looks like this:
    MNNConditional.JPG
    HTH

    Test File: MNN-Customer Property Control log.xlsx
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks but what I want to see is columns M, O, Q become red if there is a date in columns L, N, P (30,60,90 Day Notice date columns).
    If there is no date in these 30,60,90 Day Notice date columns, then the "Notice Mailed" columns do not need any Highlighting.

    Thanks in advance for your assistance.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    MNN,

    You just need to adjust the columns you apply the conditional formatting formula to by 1 column. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks for the insight. Could you give me hand understanding how the Conditional Formatting formula works =AND(NOW()>L7,L7<>"

    Thanks again.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Conditional formatting works to format a cell a specified way when the condition setup is TRUE. the formula:
    =AND(NOW()>L7,L7<>"")

    Will be TRUE under 2 criteria: when the current date and time [=NOW()] is greater than the value of L7 AND when L7 is not blank (or a null string).

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •