Is there a Formula (Set of Formulas) that can check multiple dropdowns from other data column?

Casey

I have a Metrics sheet in which I'm trying to populate a dropdown list (multiple values) with building types from client portfolios.

As an example Client A could have 5 buildings: 3 Office, 1 Industrial, and 1 Warehouse; Client B has only 5 buildings as well: but all are Office. The client buildings are in a sheet with all of their basic details listed out in rows per building. I want to populate a dropdown list in a metrics sheet with the distinct building types that a client has within the primary data sheet.

In the example above Client A should have "Office"; "Industrial", and "Warehouse" selected in the metrics dropdown list whereas Client B would only have "Office". I want that to be populated by a formula but so far all that I've been able to use is JOIN(DISTINCT(COLLECT))) which works but it changes from a dropdown to a running text list which isn't ideal.

Thanks in advance,

Casey

Brian_Richardson

Oh got it, I misunderstood. There isn’t a way to drive the selection options in one drop-down from the choice in another field. I wish there was.

I’ve worked around this on a form by creating a drop-down column for each sub-selection and then using the form logic to show the appropriate sub-selection column on the form based on the first drop-down choice. Then piece it back together with a formula on your sheet. That works pretty well for forms.

For example we have a new project form with Region and Country. In the sheet I have a North America Countries drop-down column, a South American Countries drop down column, etc. On the form I put all country fields, then for the Region field I add logic to show North American Countries field when Region=North America, etc.

Brian_Richardson

There's two primary options to populate dropdown lists dynamically - they are both addon options that you need to buy either as part of the Advanced package or ala carte.

Option 1 is to use Data Shuttle. Data Shuttle will read the contents of one sheet or report and export it as a file. You can then use Data Shuttle in a second workflow that imports the content of the file as options for a dropdown list.

Option 2 is to use Bridge. Bridge lets you use the API to read data from a sheet and push it to a column as options. It's harder to setup initially, but faster than Data Shuttle once setup. Here's the article explaining how to set it up: https://help.smartsheet.com/articles/2482712-update-dropdown-columns

A "poor man's" way to do this would be periodically open the Client sheets and copy/paste to your column dropdown list.

Or if you have other API integration tools that you use, you can leverage the Smartsheet API directly to do the same thing that Bridge does.

Casey

@Brian_Richardson my company does have Data Shuttle and I've used this to create dropdown options via another list before. However, the goal would be to select certain dropdown items within the list based on another criteria (in my case a client ID).

So I have created a dropdown list of any/all building types within my list regardless of the client in my metrics sheet using Data Shuttle. The step I'm looking for is to for the metrics sheet to take the clientID @row and check/select the options within the dropdown list column that correlate to that Client in the primary list. Are you saying Data Shuttle could accomplish this as well?

Thanks again!

Brian_Richardson

Oh got it, I misunderstood. There isn’t a way to drive the selection options in one drop-down from the choice in another field. I wish there was.

I’ve worked around this on a form by creating a drop-down column for each sub-selection and then using the form logic to show the appropriate sub-selection column on the form based on the first drop-down choice. Then piece it back together with a formula on your sheet. That works pretty well for forms.

For example we have a new project form with Region and Country. In the sheet I have a North America Countries drop-down column, a South American Countries drop down column, etc. On the form I put all country fields, then for the Region field I add logic to show North American Countries field when Region=North America, etc.