Dynamic Drop Down lists from another column either within the sheet or from another sheet

Byron Hu

I've searched, but everyone seems to say that this is not an option at the moment, are there any work arounds?

I want to create a page where someone can create a product id number based on several factors, i.e. category, model number, color, country, end customer

I'f like osmeone to be able to select the category (call it YYY), then be presented with a list of options for model number (XXX), then select a color (CCC), a country (NNN), then end customer (MMM) if applicable, which would then display the final product id (YYYXXX-CCC-NNN-MMM would be the final output displayed)

Is this possible on a dashboard?

Neil Watson

@Byron Hu the solution depends on the relationships between the category, model number, colour, country and end customer.

For example, are the model numbers, colours, countries & end customers the same (static) irrespective of which category is selected?

Or does selecting category YYY mean a different set of model numbers, colours, countries & end customers each time?

If each set is the same then you can use a function such as JOIN to add the selected results into a final product ID.

Neil Watson

@Byron Hu the solution depends on the relationships between the category, model number, colour, country and end customer.

For example, are the model numbers, colours, countries & end customers the same (static) irrespective of which category is selected?

Or does selecting category YYY mean a different set of model numbers, colours, countries & end customers each time?

If each set is the same then you can use a function such as JOIN to add the selected results into a final product ID.

Andrée Starå

Hi @Byron Hu

I hope you're well and safe!

To add to Neil Watson's excellent advice/answer.

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to match the values to a specific ID and then use JOIN to create the Product ID.

Another option could be a form and Conditional logic structure.

More info: 

https://help.smartsheet.com/articles/2481701-use-conditional-logic-to-streamline-form-submissions

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Byron Hu

Thanks Andree and Neil.

Typically each category would have a different set of colors, although I could just make everything the same for all categories, although that could make the drop down list a bit unusable. Do you have an example of where I would have the cell that has the join function? Would I be able to do that on a dashboard for instance, or would it have to be on another sheet?

Byron Hu

@Andrée Starå I saw an old post where you had a work around for a interactive drop down list from another sheet, but can't find an example, can you please post a link?

Andrée Starå

@Byron Hu

Happy to help!

The cell(s) with the JOIN structure can be at the top of the sheet or in the sheet summary.

It could also be structured with a Dashboard. Make the selection, reload, and the value would show.

Make sense? Which method would you prefer?

Regarding my workaround with the interactive drop-down list.

I'd be happy to share an example. 

Please send me an email at andree@workbold.com, and I'll share it with you.

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Byron Hu

@Andrée Starå i will send an email. Regarding the join function on a dashboard, what type of widget would I use? I’m trying to wrap my head around how I would do it and I can’t figure it out.

if I do it within the sheer, what formula would I use?

thanks for all tour help!

Andrée Starå

@Byron Hu

I'd recommend using a Metric Widget because then we can show the JOINED information.

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Regarding the formula.

It would look something like this.

=JOIN([Order Date]@row:[Ship Date]@row, " - ")

https://help.smartsheet.com/function/join

Byron Hu

@Andrée Starå Thank you for all your help. How would i go about making selections for each category/color? using a metric widget doesn't let me change the selection.

Thanks again!

DonMc

What was this solution? It seems to be pretty much the only game in town without paying someone else or buying more smartsheet features.

Byron Hu

Hi Don, I ended up using a combination of drop down lists and the join function. Still no dynamic drop down yet, and never heard back from Andree about his solution to a dynamic dropdown

Andrée Starå

Hi @DonMc & @Byron Hu

I hope you're well and safe!

@Byron, I did email you back with the solution and also later again to ask if you'd solved it, but I didn't get a response, so I figured you'd solved it.

• My other workaround is below

https://share.getcloudapp.com/kpuBzPkG#

PDF: https://hubs.ly/H0Kdnzf0

• Another option could be to use the premium app, Data Shuttle.

I developed a client solution with it to keep dropdown lists synced.

Would any of those options work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!