Formula or Conditional Formatting for Double Booking Staff on Gantt chart

JayKPM
JayKPM ✭✭
edited 08/01/22 in Formulas and Functions

Hello

Please find screenshot of the Sheet attached.

I would like to achieve one of the two options below preferably the first!

If one of the staff members is booked for the same day on multiple jobs to;

  1. Highlight both jobs and the fields which are causing the problem or;
  2. To give a red symbol in the "Valid" column

Can someone help me with either a formula or a way of adding this in conditional formatting?

Thanks in advance


Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You are going to have to use a formula on the sheet anyway to get Conditional Formatting to work, so we may as well leverage the "Valid" column.


    =IF(COUNT(CHILDREN([Task Name]@row)) = 0, IF(COUNTIFS(Start:Start, @cell<= Finish@row, Finish:Finish, @cell>= Start@row, [Assigned To]:[Assigned To], @cell = [Assigned To]@row)> 1, "Red"))


    From there you would set up conditional formatting to trigger off of the Valid column.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!