Create Automation based on Multi-Select Drop Down

Kate123

Hello

I have a form where people can select what events they want to attend based on multi-select drop down option. Some of these events have a maximum capacity. I have set up formulas to count how many people sign up to each event. There are a couple of other things I want to do based on when maximum capacity is reached:

• Set up an automation to alert me when 20 people have signed up to each event so I can change the thank you message on the form
• Set up an automation to place any new registrations to a waitlist column

Any help with this would be appreciated!

Jeff Reisman

• Set up an automation to alert me when 20 people have signed up to each event so I can change the thank you message on the form

I might be inclined to set up a Metrics sheet to collect this info. One row for each event, use your count formula to populate the number of people signed up, and add automation rule to email you when the number is greater than 19.

• Set up an automation to place any new registrations to a waitlist column

In your main sheet, add a waitlist column and a total registrant column. In the total registrant column, use a COUNTIF to collect the total number or rows where the event name equals the event for this row: =COUNTIF(Event:Event, =Event@row), and make this a column formula.

Create an Change Cell Value automation rule that is triggered when new rows are added. Add a condition block to the rule that if total registrant column is greater than or equal to 20, change the cell value in the waitlist column to whatever you want (you could use a checkbox, or just make it a "1" in the waitlist column, etc.) (You could also use the waitlist column to send an alert to the registrant that they're waitlisted.)

Kate123

Thanks Jeff, how does this work if multiple options were selected in the one row? So on the registration form a person can select up to 4 options.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/315866#Comment_315866

In that case, it probably makes more sense to move the total registrant count to the metrics sheet. List each Event in a column, and in a Total column, use:

=COUNTIF({Main Sheet Event Column}, CONTAINS(Event@row, @cell))

In the metrics sheet, also create a Waitlisted column:

=IF(Total@row >20, "Yes", "No")

The tough part would be actually indicating which event the user registered for that is waitlisted... 🤔

In the Main sheet Waitlist column, try this out - I'm not making any guarantees though, I've never done this before!

=JOIN(COLLECT({Metrics Sheet Event Column}, {Metrics Sheet Waitlisted Column}, "Yes", {Metrics Sheet Event Column}, CONTAINS(Event@row, @cell)), ", ")

This SHOULD collect any events from the Metrics sheet event column which are waitlisted and also appear in the multi-select Event field for this row, and list them separated by commas, like

Training Event, Toga Party, Copy Machine Usage Seminar

You could then use automation triggered when a row is added that looks for any value in the Waitlisted column and sends an alert to the registrant and/or the admin that the listed events are Waitlisted.

Kate123

thank you so much!!