Dynamic Data Validation with Columns from another Grid
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!
Best Answer
-
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.
Answers
-
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.
-
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!
-
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?
-
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:
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@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 ar
ecrucial.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?
-
@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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
No cell linking is one-to-one. You'll have to repeat it for each cell you're linking too.
-
@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!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@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?
-
Right. The Data Shuttle is updating the values in the column properties (what options are available). It is not actually updating cell values.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Andrée Starå Are you using a method other than Data Shuttle?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
No, I'm using Data Shuttle as well.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@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?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives