Need some suggestions with highlighting info on a tracker


Hello everyone,

I am looking for some creative support with a sheet that compiles milestones and dates from other trackers to an aggregated sheet with linked cells.

The purpose is to share dates/milestones for each project with each column being a different project and the vertical axis being the milestone or deliverable date of completion. So I have defined a list of milestones and owners and formatted each column to link the cell to the individual project tracker so that the date will auto-populate as its entered or adjusted in the singular project plan.

My senior leadership likes the idea, but has asked for bringing attention to dates that have moved and or gone past the estimated timeline (e.g, T-60 from launch).

My dilemma is that all the dependencies and date functions are in my smaller project plans, so if I want to highlight a cell that the timeline has gone past the estimated timeline, how would I best used conditional formatting or automation to capture the overdue deliverable?

Thanks in advance,



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide some screenshots for reference?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Pete Photis

    I agree that it would be helpful to see screen captures, but please block out sensitive data.

    It sounds like you may need one helper column per-project (say a Checkbox column) that's specifically comparing the date that you have pulled through in its column against that specific Project Launch Date (and checking the box if that row's date is past the launch date for that Project). Then you would build a Rule that says if the box is checked in the helper column, highlight the date in the other formula column.

    You can hide all of your checkboxes, so you only see the data columns, but there does need to be some sort of indicator in this tracker sheet for the formatting rule to read. I hope that makes sense!