Blocking Actions on a Spreadsheet

Simon Barker

We use Smartsheet for our work holiday/vacation planner. We don't want holiday overlaps so I would like to make it impossible for 2 employees to select the same dates. Is there a way to block actions?

Is this something that can be done?

Thanks in advance!

L_123

You cannot block an action. You can send an email that the date is already taken, and highlight rows that conflict with each other. I am assuming you are using a standard project sheet for the tracker as that is the easiest way to do it.

To add the conflict manager you need to add a new column. It doesn't matter what the name is. Add the formula below and drag it down.

=IF(COUNT(COLLECT(Duration:Duration, Start:Start, AND(@cell <= Finish@row, @cell >= Start@row))) + COUNT(COLLECT(Duration:Duration, Finish:Finish, AND(@cell <= Finish@row, @cell >= Start@row))) > 2, "Error", "")

I recommend locking the row once you have done this. Then you create a conditional format that highlights the row if the new row contains the word error. You can also send a notification that there is a conflict in the schedule if the column changes to error. This can be sent either to you, or to all the users who have conflicts.

Simon Barker

Hi Luke,

Thank you very much!

There's no way I could have figured that out myself. Do you know how to only apply this to specific people. We have an employee column with the names of each employee. I have attached a screenshot to display this. If I want to apply the conflict to just 2 names, how would I do this? i.e. if James and Sam pick the same holiday only then with an error display.

Thanks again!

J. Craig Williams

You really shouldn't post a picture showing your team has 5w of PTO. Most Americans don't realize that is possible or the norm in most of the rest of the world.

We are the only industrialized nation on the planet that has no minimum annual leave or paid public holidays.

https://en.wikipedia.org/wiki/List_of_minimum_annual_leave_by_country

Craig

L_123

=if(or(Employee@row = "James",Employee@row = "Sam"),IF(COUNT(COLLECT(Duration:Duration, Start:Start, AND(@cell <= Finish@row, @cell >= Start@row))) + COUNT(COLLECT(Duration:Duration, Finish:Finish, AND(@cell <= Finish@row, @cell >= Start@row))) > 2, "Error", ""),"")

 

Try something like that. 

L_123

you can do a cell reference instead of the names, and make a section in the top rows to have a variable search as well.

L_123

=

IF(

COUNT(COLLECT(Employee:Employee, [Start Date]:[Start Date], AND(@cell <= [Finish Date]@row, @cell >= [Start Date]@row,employee:employee,or(@cell = employee$1,@cell = employee$2)))) + 

COUNT(COLLECT(Employee:Employee, [Finish Date]:[Finish Date], AND(@cell <= [Finish Date]@row, @cell >= [Start Date]@row,employee:employee,or(@cell = employee$1,@cell = employee$2))))

 > 2, "Error", "")

 

this will have the same effect as the formula posted above, but I think it has a little more flexibility in looking at different options.

In order to understand how to do this you need to know the collect formula, then you can take a look at adding AND + OR to the employee category and build your list of criteria.  or just leave it like it is and have it be flexible. 

 

Right now the formula references the first 2 values of the employee row for analysis.

https://help.smartsheet.com/function/collect