Create dynamic dropdown lists in forms

This article provides step-by-step instructions on creating a dynamic dropdown list in a Smartsheet form. This setup is useful for inventory or scheduling use cases, such as managing appointment time slots, to ensure that selected slots remain unavailable for future form submissions.

Who can use this?

Plans:

  • Pro
  • Business
  • Enterprise

Permissions:

  • Editor
  • Admin
  • Owner

Find out if this capability is included in Smartsheet Regions or Smartsheet Gov.

Dynamic dropdowns in Smartsheet forms enhance real-time inventory management by linking dropdown options to the statuses of items in a source sheet.

When users select an item in the form, the system automatically marks it as unavailable in the source sheet, removing it from the dropdown list for others. This approach prevents double-booking and ensures data accuracy, facilitating efficient scheduling and resource allocation.

To achieve this dynamic behavior in a form, you need two separate sheets and a VLOOKUP formula:

  1. Sheet 1 (data source): This sheet contains the full list of available time slots and tracks which slots have been selected.
    • Times (Column 1): Lists all possible time slots. For example, 1:00 p.m., 2:00 p.m., 3:00 p.m.
    • Is Selected (Column 2): A Text/Number column that holds a VLOOKUP formula. It automatically updates when someone selects a time slot in the form.
    • Available Time Slots (Column 3): This is a Text/Number column that holds an IF/IFERROR formula to determine which time slots are still available.
  2. Sheet 2 (form source): Use this sheet to create the form. It contains a dropdown column that links back to the Available Time Slots column on Sheet 1.

Follow these steps in Table view of your sheets to set up the dynamic dropdown list.

Step 1: Set up the data source sheet

  1. In Sheet 1, create the following three columns in Table view:
    • Times (Text/Number column type).
    • Is Selected (Text/Number column type).
    • Available Time Slots (Text/Number column type).

  2. Enter the time slots into the Times column. Leave the other columns blank for now.

    Enter time slots in the Time Slots column

Step 2: Create the dropdown in the form source sheet

  1. Navigate to Sheet 2 and ensure you're in Table view.

  2. Right-click on the desired column header and select Column properties. This example uses the Set Up Time Slot column.

    Edit column properties for the Set Up Time Slot column

  3. Change the Column type to Dropdown list.

    Change the Set Up Time Slot column type to Dropdown list

  4. Under the Dropdown list options, toggle on Link to another sheet.

    Link to another sheet in the Set Up Time Slot column
  5. In the Source sheet* field, paste the link of Sheet 1.

  6. Select the Available Time Slots column from Sheet 1 as the Source column* for the dropdown.

    Select the Available Time Slots column as the Source column for the dropdown

  7. Select Apply.

    This step links the dropdown choices in your form to the time slots marked as available based on the formula in Sheet 1, which you set up in the next steps.

Create the dropdown in the form source sheet

Step 3: Add the VLOOKUP formula to track selections

You can also use an INDEX/MATCH formula as an alternative to the VLOOKUP function.

  1. Go back to Sheet 1 and switch over to Grid view.

  2. In the first cell of the Is Selected column, enter the following VLOOKUP formula:

    =VLOOKUP(Times@row,

  3. In the tooltip that displays, select Reference Another Sheet.

    Reference another sheet using the VLOOKUP formula

  4. Use the Search for a data source field in the top left to find and select Sheet 2. In this example, Sheet 2 is referred to as DD in Forms 2.

    Search for your sheet using the search for a data source option

  5. Select the column header from Sheet 2 and select Insert Reference. This example uses the Set Up Time Slot column.

    Select your column header to insert the reference

  6. Update the formula in the Is Selected column to the following:

    =VLOOKUP(Times@row, {DD in Forms 2 Range 1}, 1, false)

    • Times@row: This is the search value.
    • {DD in Forms 2 Range 1}: This refers to the range of the columns in Sheet 2 that contain the selected time slot. It looks at the primary column and the column where the selection occurs.
    • 1: This is the column index from the range to return; in other words, the selected time slot itself.
    • False: This indicates an approximate match.
    Edit the VLOOKUP form
  7. Press Enter. Since no one has selected a time slot in the form, this cell displays #NO MATCH. This is the correct initial result.

  8. Right-click the cell and select Convert to Column Formula to apply it to all rows in the Is Selected column.

    Convert the Is Selected column to column formula

Step 4: Add the IF/IFERROR formula to determine availability

  1. In the first cell of the Available Time Slots column in Sheet 1, enter the following IF/IFERROR formula:

    =IF(IFERROR([Is Selected]@row, true) = true, Times@row,  "")

    This formula checks if the Is Selected column resulted in an error (#NO MATCH) and, if so, returns the time slot, indicating it's still available.

    • [Is Selected]@row: References the result of the VLOOKUP in the current row.
    • = true: Checks if the time slot has been selected.
    • Times@row: If the time slot hasn’t been selected (the VLOOKUP returns an error), the formula returns the time slot text, making it available.
    • “”: If someone selects a time slot using the form, the formula returns a blank value, making it unavailable in the dropdown.

  2. Press Enter.

    Add the IF/IFERROR formula
  3. Right-click the cell and select Convert to Column Formula to apply it to all rows in the Available Time Slots column.

Step 5: Create and test the form

  1. Navigate back to Sheet 2 and select Forms > Create form.
  2. Customize the form by keeping only the Set Up Time Slot field (or the name of your dropdown column) and select Save.

  3. Select Open Form.

    Open the form

  4. In the opened form, choose a time slot and select Submit.

    Choose a time slot and submit

  5. Go back to Sheet 1. You can see that the Is Selected column shows the time you just submitted, and the Available Time Slots column is now blank for that row.

    Check the Is Selected column for the time you submitted

  6. Return to the form and refresh your browser to view the updated dropdown list.

    View the updated dropdown list

    There may be a delay of up to one minute between form submission and the sheet update/form refreshing the dropdown choices. After a refresh, the time slot you selected disappears, confirming the setup works correctly.