Form Submission w/ updating dropdown list - HOW TO

Christian OG

Use Case: Stakeholder requested available time slots for events at different days to be removed from the dropdown list once the time slot is full

Solution:

1. Create independent sheets for each day with the corresponding times
2. Create a master sheet that contains all of the days and times
3. On the independent sheets, have an available column with a column formula similar to this one - =IF(COUNTIFS({Bookings - Session Date}, Date@row, {Session Bookings-Session Time}, HAS(@cell, Time@row)) > 0, "booked", Time@row)
4. On the sheet that is associated with the form (Session Bookings in my example), for the date column, enable 'link to another sheet' and use the date column from the master sheet
5. On your form, you will have a dropdown list for all available dates that matches that from your master sheet (Date Field)
6. For each date, you will have a corresponding Time Field that is triggered via logic. That logic is to grab the times from the independent sheet you created in Step 1
7. On the form associated with the form, the columns that are for each date, adjust the column properties to 'link to another sheet', choose the matching independent sheet you created in Step 1 and select 'filter dropdown options' to the 'available' column from the sheet that 'is not equal to 'booked' from the sample formula in Step 3

Loom Explainer