Convert Value To Static

Question

I need help, please!

I created a process that allows employees to request deactivation periods from their snow assignment. The ogranization allows for 5 employees for days and 5 employees from nights to deactivate at one time, per day. I am using the following formula to retrieve the number of slots available:

=IFERROR(INDEX({LSSlotsLeft}, MATCH([Day 1]@row, {LSStartDate}, 0)), "")

Everything is working, I realized an issue. As employees select days to deactivate and should they choose the same day, the number of slots will go down. Generally, this is not an issue, but on popular days like 12/25, more than 5 will request it and the previously approved will now change to denied.

I need the LSSlotsLeft to become static. I can't move the rows to another sheet because then the calendar will not show the correct slots left.

Day 1  -

=[Start Date]@row

Day 1  Available-

=IFERROR(INDEX({LSSlotsLeft}, MATCH([Day 1]@row, {LSStartDate}, 0)), "")

Day 1 Not Available -

=IF(OR(AND(ISNUMBER([Day 1 Available]@row), [Day 1 Available]@row <= 0)), 1, 0)

Thank you,

Ron Anderson

Ronald Anderson · Answer

Snow Staffing dictates the number of individuals that can be absent per shift/per day. That current number is 5. To receive an approved deactivation, all day(s) must be available. The caveat being that all denials are reviewed by the snow managers and can be approved with the manager override.

Paul Newcome · Answer

Ok. And to make sure I understand…

You want to be able to lock in the first 5 approved submitters per day? What if someone applies for multiple days (such as row id 1400) and maybe the last day isn't available because 5 people already applied and were approved for it?

Ronald Anderson · Answer

I'm sorry, here are the corrected links:

Deactivation Process

Open Slots