Is it possible to add a yes/no box in column that adds current date when selected "yes" and highlights a row of data?

Posted on 2014-08-13
Last Modified: 2014-08-14
Condition 1: I want to add a Yes/No checkbox in J3:  EPR CLOSED OUT (YES/NO), and if selected "Yes" it automatically populate I3:ACTUAL CLOSEOUT DATE, with current date.  If J3 is not selected it populates I3 with "N/A".

Condition 2:  If K3:ACTUAL CLOSEOUT DATE  is between date ranges of G3: INITIAL ACA CLOSEOUT & I3:CLOSEOUT          DATE               (CO + 30 DAYS)  highlight Green, If date exceeds specified range highlight record red
Question by:cfhasan1
    LVL 27

    Accepted Solution

    Condition1:  Rather than use a check box, I would recommend Data Validation on the cells in column J to only allow "Yes" or "No" (or empty).  It's easier to maintain and still allows a droplist feature to allow selecting one of the two values instead of typing.

    The formula in cell K3 (Actual Closeout Date) would then be:

    Condition2:  One can use a conditional formatting rule to make this happen:

    Important:  One issue with the formula in Condition 1 is that the values in column K will always display the current date if their associated values in column J are "Yes."  This may be an issue going forward and will need to be addressed.  Right now, these are, at best, default values from formulas that will need to be replaced with actual values.  Unfortunately, that act will destroy the formulas.  It may be that a automated solution using VBA will need to be implemented to help maintain this worksheet.

    I've attached a modified version of your workbook with these changes.


    Author Closing Comment

    I'll have to address date issue.  It's vital that the actual date remains static.  Additionally, I'll need to add conditional format for when date exceeds the closeout date i.e. =$k3> $I3 turns cell red?
    LVL 27

    Expert Comment

    by:Glenn Ray
    It does appear that a VBA solution will be needed then so that these default values will be inserted, rather than formulas.

    In the interim, I've created a second conditional formatting rule that turns the Actual Closeout Date cell red if it falls after the closeout date in column I using this rule:

    I think it may be best if a new question is asked that specifically requests a VBA or automated solution to your issue.  That is, a procedure that automatically enters today's date as the Actual Closeout Date when the related EPR value is changed to "Yes", or changes the Actual Closeout Date to "N/A" when the EPR closed value is "No" or "Blank".  

    You'll need to think through your regular use of this sheet to envision different scenarios of adding/removing/changing data to ensure that all possibilities are captured.


    Author Comment

    LVL 27

    Expert Comment

    by:Glenn Ray
    Whoops...forgot file.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
    Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now