How if a box is checked it any point in the process it will mark it checked for that section
Answers
-
Hi @Kelly Moore
The sheet is working. However we added a column in "day before due date" and it will send an alert the day before a due date if something is not marked complete or N/A. We have the same columns as previous screenshots. How do I have the parent highlight yellow if the day before is today and not marked complete other N/A?
-
hey @Jett
If I understand, are wanting any Parent to indicate a due date - 1
You will need a helper column to find your criteria then use conditional formatting to show the yellow highlight.
Helper column (checkbox)
=IF(AND(COUNT(CHILDREN([Primary Column]@row))>0, TODAY()=[Day before due date]@row),1)
create a conditional formatting rule when this helper column is checked
Will this work for you?Kelly
-
Hey @Kelly Moore
I have a question I would like the parent level to highlight yellow when the day before due date is today. I have a formula but it seems to only be returning a 1 on the child level. I need the one on the parent levels. If it was on the main parent that would be great so when all rows are collapsed it will be yellow indicating something is due soon for that item.
-
Hey @Jett
Are you using the formula I suggested above? This should flag any Parent, regardless of their level.
Kelly -
Yes and it is not returning any values. : (
-
Hey @Jett
Sorry, I didn't realize the Parent row didn't have the relevant date in the Day before Due Date column. This should do it for you. It should check the top level Parent as well as any Parent below it
=IF(AND(OR(COUNT(ANCESTORS([Primary Column]@row)) = 0, COUNT(CHILDREN([Primary Column]@row)) > 0), COUNTIFS(DESCENDANTS([Day before Due date]@row), TODAY() = @cell) > 0), 1)
And if you don't already have the Complete vs incomplete criteria sorted in another column, your formula will be:
=IF(AND(OR(COUNT(ANCESTORS([Primary Column]@row)) = 0, COUNT(CHILDREN([Primary Column]@row)) > 0), COUNTIFS(DESCENDANTS([Day before Due date]@row), TODAY() = @cell, DESCENDANTS(Status@row), AND(@cell<>"Complete", @cell<>"N/A")) > 0), 1)
Will this work for you?
Kelly -
@Kelly Moore Hi,
This is working however when a status is marked complete or N/A then I need it to make the box unchecked. So if it is complete or N/A there is no need to worry about the task.
-
Using my formula above in my test sheet, I have check marks for the Parent and Grandparent rows:
- IF the row is a Parent row
- AND any Child is Today's date
- AND the Status of any Child is not Complete or NA
Are you wanting your Child rows to be checked as well? I must have misunderstood your original request.
Try this if you also wish Child rows to be checked:
=IF(AND(OR(COUNT(ANCESTORS([Primary Column]@row)) = 0, COUNT(CHILDREN([Primary Column]@row)) > 0), COUNTIFS(DESCENDANTS([Day before Due date]@row), TODAY() = @cell, DESCENDANTS(Status@row), AND(@ )) > 0), 1, IF(AND(COUNT(CHILDREN([Primary Column]@row)) = 0, [Day before Due date]@row = TODAY(), Status@row <> "Complete", Status@row <> "N/A"), 1))
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!