Change Cell Automation Based on Count of Items

Kate123

Hello

I am trying to create an automation that a waitlist column is "checked" when the total registrations exceeds 10. I've done this before using the auto-number feature to count registrations as they come in from the form, and then created the automation based on that column.

However, I am trying to do this now based on people having two time options to choose from and the numbers are capped at 10 per session. Is it possible to build an automation to automatically check the waitlist column if registrations for each time option exceeds 10?

Thanks!

Danielle Arteaga

Hi, Kate. It would be really helpful to see the structure of your worksheet. Can you provide a screenshot. (You can blur out any sensitive data or just send a shot of your column headers.) Once I can see that, I can help you write a formula to do what you describe.

Kate123

Thanks Danielle! Here is a screenshot of the form and the sheet - let me know if you need to clarify anything on this

Nick Korna

Hi @Kate123

You can do this by adding another column (in this example I've called it "Count"), with the formula:

=COUNTIF(Attendance:Attendance, Attendance@row)

You can then configure automation like this:

Obviously, your A and B in the Trigger would be your dates and your condition greater than would be 10 instead.

If your submissions are coming in via form then you should end up something like this:

Where the registrations up to the limit (5 in the example, 10 for you) remain unticked and any additional are then ticked.

Hopefully this helps, but if you've any more questions then ask away!

Kate123

Hi Nick - thanks so much for your help!

For some reason, it seems to be checking the waitlist for every row and not just the ones where it exceeds the limit. Below are some screenshots - can you see what I am doing wrong?

I just manually selected the options on the sheet and did not put them through the form - would this be why?

Nick Korna

Hi @Kate123 ,

The automation somewhat breaks if you put a lot of values in during the same minute. Example below same automation values as example above but with C & D added):

It works fine otherwise, but you'd probably have to import your data in a tiny bit slower (i.e. just shy of the limit and then the remainder).

I'm not sure if there is a better solution that would get round this limitation. 🤨

Kate123

Thank you, Nick! That worked perfectly when I tested at a slower pace :-) The entries from the form will be more staggered so that should not be an issue in "real life"!