How to reset a formula after the next submission is made

method90

Hi All,

Having issues figuring out the following. I've created a check in/out form for people moving around a facility. The form captures their time and slots them into the correct timeframe to display on a dashboard our staffing levels against required/scheduled. In some cases, I envision the staff might forget to check-out on time within the timeframe and would showcase a (1) for example under 1545-2215.

I want to zero out the (1) once a submission is made in the 2145-0015 category. I can do this in a helper column with an IF statement. But, when that time rolls back around the next day that data will still be in the sheet therefore showing 1 person is checked in.

The visualization on the dashboard would then only showcase the timeslot we're currently operating in, i.e. only 0545-1415 would show since it's (9) and all others wouldn't since they would be zeroed out.

Any solution for this? Let me know if any questions.

MVP OPS

Hello there,

I would think you could use the change cell value or clear cell value automation to accomplish this task based on your IF formula.

I hope this helps.

best,

Brad

MVP OPS

method90

Hi Brad,

I did take a look at that but wasn't sure how it would work in my case. Essentially, we have a form where staff make form submissions and those submissions update the chart above demonstrating staffing level compliance.

Currently showing as so:

Understanding that some people might forget to check-out of their area on time then that might affect the next time step change (i.e. from 0445-0615), if someone checks out late then it might show -1 on the next time step change. So, I was hoping to somehow reset each time step change if a new submission is made that falls into the next time step change.

Hope that makes a bit more sense. Furthermore, then instead of showcasing this whole table I can only display the row that has "Actual On-Site" which would always be only 1 row (current timeframe) since others are zeroed out.

MVP OPS

Thanks for the clarification,

So the automation would be from the sheet where the form responses are being captured. I'm assuming that your table above is rolling up values from the sheet linked to the form inputs.

Depending on what you are counting in your sheet to input the [Actual on site] number, the automations for clearing cell or changing cell value would be applied to the column being counted. You may need to add an index match look up formula to check whether a person is checked into more than one area that could trigger the automation.

It's hard to be of more help without more insights of your base sheet. But I hope some of this makes some since.

method90

Hi Brad,

My form submissions and formulas for actual on-site, etc. are all on the same forum. I just added a few columns on the right side of the form submissions which is then pulled into a report for the dashboard link.

I suppose the data I would want to zero out would be in the form submissions? i.e. if a new timeframe is met then zero out other form submissions?

Unsure how the workflow/logic would work here, never used this type of automation. Any idea? Let me know if you need more details.

MVP OPS

Got it,

Yea, you are correct on the automations being in your form submissions page.

To keep this simple you could create a nested if statement starting at the bottom of your table.

Depending on what you are counting to put the the [Actual On-Site] digits in the table. You would want to clear that value from the form submission row.

How you would do that is by using a nested if statement that says something like if(2145-0015@row>0, "Clear all". Then set automation to clear your counted value in each row that don't have the 2145-0015 response in it.

If you want to share a screenshot of your sheet I can attempt to create the if statement.

hope this helps

method90

Hi Brad,

That would be helpful. I've hidden the sensitive data such as employee names and locations but those areas are there and I have a CONCAT running which I could simply switch out when I see how you build out this logic.

IO = in and out

Step Ref, Matrix Ref, Required Staffing, Actual On-site columns are where I'm running my formulas and creating reports for dashboards from.

Form Submissions fall into all columns before Shift to Created columns.

Let me know if that makes senses.

MVP OPS

Hi @method90 ,

I went through and built this and created a video cause there is a lot going on here. I hope this helps.

https://www.youtube.com/watch?v=JIr5z4lfLh0