[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 645
  • Last Modified:

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

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
EE-Evaluation-Tracker.xlsx
0
cfhasan1
Asked:
cfhasan1
  • 3
  • 2
1 Solution
 
Glenn RayExcel VBA DeveloperCommented:
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:
=IF(J3="Yes",TODAY(),"N/A")

Condition2:  One can use a conditional formatting rule to make this happen:
=AND($K3>=$G3,$K3<=$I3,$K3<>"N/A")

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.

Regards,
-Glenn
EE-Evaluation-Tracker.xlsx
0
 
cfhasan1Author Commented:
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?
0
 
Glenn RayExcel VBA DeveloperCommented:
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:
=AND($K3>$I3,$K3<>"N/A")

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.

-Glenn
0
 
cfhasan1Author Commented:
Thanks!
0
 
Glenn RayExcel VBA DeveloperCommented:
Whoops...forgot file.
EE-Evaluation-Tracker.xlsx
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now