Replace value if overdue

Desertbird

Hello,

We have a sheet to track teams and supervisors that we update weekly with new hire employees. Each "seat" is it's own row, and to show team size, empty seats are labeled as "Open Spot." If we have a new hire coming in, we put them with their projected team and projected start. On their start date, the other cells in the row are updated with things like their ID and Schedule, etc. However, not all new hires show up. This causes a lot of housekeeping going through the taking out the "reserved" seats each week.

My question is: is there a way to set a cell to change back to "Open Spot" once the start date is reached and no other cells in the row have been updated with their ID's?

Thanks in advance for any input!

Stanley Blume

You should be able to resolve this with automation.  Under the automation menu, the "Change a Cell Value..." should work nicely in this case.

You can set the trigger to be when a date field is reached.  You could create a reference field that is number of days after the expected start date or could have this trigger run every week at a specific time.  For example, if new hires always arrive on Monday then you could set it run Tuesday mornings.

Under Conditions, you can filter what rows will be impacted.  In this case you might want to look for Blank ID Cells and Start Dates that occurred in the past.

Finally, you can set the Change cell value to "open status" for those that meet the conditions.

Hope this makes sense!