How to construct a multi sheet tracking log?
Disclaimer: i don't yet have sample sheets for this, because i've just started to try and conceptualize what i might need to do.
Background: our company has a system that requires people be called in a specific order when someone else calls in sick and we need to offer overtime to someone else to fill in. When we get to the bottom of the list, it cycles back up to the top again.
What i am thinking is that i have a list of the employees in order like;
1 - John
2 - Frank
3 - Bob
IDEA 1: and a second sheet that acts as a log of who was called, by whom, and when they were called. Ideally, i would love if when a log shows that John was called a check box would be ticked so everyone else knows John has been called for that cycle. A similar trigger would happen when Frank and Bob are called. When all the rows are checked, a trigger would uncheck all the rows, so that supervisors know John is the next to call again.
IDEA 2: The issue is triggers can only exist in the same sheet. That leads me to my other idea. Have the supervisor check the box next to the next name on the "list" sheet and that would trigger that row to be copied to the "log" sheet, then they could add the other info needed for the log. Does that seem like a better avenue to pursue than the first idea?
CONUNDRUM: no matter which idea i use, i will need a way to remove all the check boxes in the "list" sheet once they are all checked. Is there a way to know if all rows are checked/ Perhaps some data stored in a helper column?
Help Article Resources
Check out the Formula Handbook template!