How to construct a multi sheet tracking log?

Z.Win

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?

Thanks!

Nick Korna

Hi @Z.Win

You can do this on a single sheet, with a second as a log if you so wish.

Set up your sheet along the lines of:

The Count column is a simple formula:

=COUNTIF(Checkbox:Checkbox, 1)

You can then set up automation to autoclear the checkbox column when everyone has had their checkbox ticked:

The number would be changed depending on how long your employee list is - here it is 7, for your short list it would be 3.

You can also set up the log sheet for looking over (since I'm guessing you would probably also want a comments column or whether the employee wanted the overtime/not with another checkbox, symbols or dropdown) with automation copying the rows over when the rows on the above sheet have changed.

Help this gives you some ideas, but if you've any problems/questions then just ask! 🙂

Z.Win

Nick Korna, thanks for that insight! I might take that and expand another column, to give me a total row count. That should let me make it in more a fire and forget mode, to minimize the amount of maintenance it needs.

I'll follow up if i get stuck again.