Dynamic Data Validation with Columns from another Grid

Benjamin Hatmaker

Hi,

I want to be able to validate data in a drop-down menu in one sheet from a column in another sheet.

EX: I have a sheet that serves as a master sheet of projects and the project's properties (Name, budget, etc.), and another sheet that serves as tracking tasks related to the projects in the master sheet. When People record a task being done, I want them to only be able to select names of existing projects, but I also want that list to automatically expand as we add more projects in the master sheet.

In EXCEL, I would accomplish this by assigning the relevant column in the table a name in name manager, and telling data validation to limit available responses by that column's name. The result is that I could add projects to the master table and it would add that to the drop down list created by Data Validation applied in other tables/sheets. Is there a comparable way to do this on Smartsheets?

Thanks!

Mike Wilday

Not directly. I know several people have used Cross sheet formulas to do something similar. You can have a particular sheet with just a list of approved projects which you would have to manually manage. But you could put at the top of that list: please select a project and then in your main sheet, you could cell-link to the first cell... when people double click to enter the name, it will open the linked sheet and allow users to select from the sheet any of the project options you have listed.

@Andrée Starå created a great tutorial on doing this process https://app.hubspot.com/documents/4923097/view/35612567?accessId=fd864b

Would something like that work?

Natively you would probably need to explore some premium plugins with a smartsheet rep.

https://www.smartsheet.com/marketplace/premium-apps

Mike Wilday

Not directly. I know several people have used Cross sheet formulas to do something similar. You can have a particular sheet with just a list of approved projects which you would have to manually manage. But you could put at the top of that list: please select a project and then in your main sheet, you could cell-link to the first cell... when people double click to enter the name, it will open the linked sheet and allow users to select from the sheet any of the project options you have listed.

@Andrée Starå created a great tutorial on doing this process https://app.hubspot.com/documents/4923097/view/35612567?accessId=fd864b

Would something like that work?

Natively you would probably need to explore some premium plugins with a smartsheet rep.

https://www.smartsheet.com/marketplace/premium-apps

Benjamin Hatmaker

Thanks Mike! I checked out the doc and that's super informative on the limitations and work arounds for Smartsheets. I might need something native if I want my co-workers to adopt this kind of workflow. Thanks for your answer!

Mike Wilday

Give the cell linking a try. That is native, and its pretty intuitive, but it is a workaround. You might find it suits your needs?

Andrée Starå

Hi @Benjamin Hatmaker

To add to Mike's excellent advice. (Thanks @Mike Wilday )

A solution that would be perfect for this use-case is the premium add-on, Data Uploader.

Is that an option?

More info: 

https://www.smartsheet.com/marketplace/premium-apps/data-uploader

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!

Kayla Q

@Mike Wilday I'm super confused by this document. When the document says to "Clink on ("Link to Cell in Other Sheet...)", it skips a few steps which are crucial.

Here is the first sheet containing the drop down options

The directions say to click on "Link from Cell in Other Sheet," but then fails to say what cell to link to.

If I link back to a single cell in the "SSC - Companies Dropdown" sheet, I only pull back a single value. There is not way to link multiple cells to a single cell.

What am I missing?

Paul Newcome

@Kayla Q This solution only allows for a single selection. If you want to enable a multi-selection then you will need to use the premium app Data Shuttle. If you have access to that, I would be happy to help walk you through a multi-select solution.

Mike Wilday

No cell linking is one-to-one. You'll have to repeat it for each cell you're linking too.

Kayla Q

@Paul Newcome I do have access to premium and I've been meaning to look into Data Shuttle, as I think it will be incredibly useful for many of my use cases. I would love to set up a walkthrough!

Paul Newcome

Once you get access to Data Shuttle, dynamically updating dropdown columns is actually relatively straightforward. The basic principle is that you set up an offload workflow from the sheet containing the items you want to use in your dropdown. Then you set up an upload workflow to the sheet with the dropdown column, and use the setting in the workflow to update dropdown column values.

Kayla Q

@Paul Newcome - thanks for the explanation.

I think perhaps my use case is a little different. I want to be able to allow a user to select from a drop down menu, not have a drop down option update based on some logic.

I have several sheets that all have cells with the same drop down menu options. Instead of updating 5 sheets drop down menus every time I need to add an option, it would be much easier for me to maintain a single drop down menu within a separate sheet that I could reference in other sheets, much like data validation lists in Excel.

Is this something I can do?

Paul Newcome

Right. The Data Shuttle is updating the values in the column properties (what options are available). It is not actually updating cell values.

Andrée Starå

Hi @Kayla Q

I hope you're well and safe!

To add to Paul's excellent advice/answer.

• I've developed a Master Dropdown process. Each dropdown is collected to a master sheet that is then used to distribute it out to all other columns.

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 support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@Andrée Starå Are you using a method other than Data Shuttle?

Andrée Starå

@Paul Newcome

No, I'm using Data Shuttle as well.

Paul Newcome

@Andrée Starå Is it any different than what we are already talking about where we use a sheet to house the dropdown options as row data and then use a series of Data Shuttle workflows to push it out to the other sheets' dropdown column properties?