Limit Dropdown Options Based on Cell

If there is a quality answer to this someone can point me to, feel free to link it. I've searched but find none other than that this has been requested for some time.

Here is the setup:

I have a dropdown with "Stage"; It has the following Values and is limited to one of these values.

I have another Column for "Status"; Each of these stages has a number of status types:

When Selection in Column:Stage is selected, I want to limit the dropdown options in Column:Status to only those status values appropriate to the stage.

I've placed the Stage and Status values in another table, and attempted to do this like I would in excel by setting the Status to Dropdown, and using an Index to select the appropriate column in the second table based on the value of Stage; Smartsheets Index() has a row requirement but an optional Column, so I also tried this using the data in a horizontal configuration (1 row per state with status options).

I've attempted various forms of INDEX(COLLECT()), INDEX(), INDEX(MATCH())

Any assistance would be greatly appreciated.

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!