Drop down list from another sheet

Wendy Jacobs

I agree. This is a basic function of almost all spreadsheets. It's so frustrating that Smartsheet requires continual workarounds or the purchase of expensive third party apps.

SheetSheppard

A simple function for sure. Smartsheet is not a database, it is a online spreadsheet missing many basic features.

gjgreetan

I had a use case like this and found a solution using Data Shuttle.

My scenario was: I have a sheet of Change Tickets (Sheet A), and I have another Sheet of Change Requests (Sheet B). I wanted to update a column in the Change Ticket sheet (Sheet A) w/ a list of valid Change Request IDs to pick from coming from the Change Requests Sheet (Sheet B).

Step 1: Create a new scheduled offload task using Data Shuttle. We need to "offload" the list of Change Requests from our Change Request sheet (Sheet B). Setup your "Target" for this to be an attachment to the same sheet your are extracting from (Sheet B). You can specify CSV or XLSX as your output and then determine the contents of the file (i.e., which columns you want). You then schedule this to run regularly -- the lowest increment possible is every 15 minutes. The end result is that Data Shuttle will create a CSV/XLSX extract of the contents of your SmartSheet, and then attach it to that very same SmartSheet. Subsequent runs create new versions of that same file, which overlay the old versions. You can view the contents by selecting "attachments" icon on the SmartSheet.

Step 2: Create a new scheduled upload task using Data Shuttle. We now want to update the list of values in a Drop Down list. For your source, pick "SmartSheet Attachment" and then we'll select the SmartSheet where our attachment from Step 1 lives (Sheet B). It will ask you to choose the attachment, I pick "Most Recent" and then select the name of the attachment. If it worked, you should see the name of the CSV/XLSX show up in the list from Step 1. Walk through the rest of the Data Shuttle configuration as you would normally, but on the "Run Options" step, select "Run on Attachment". This will trigger this shuttle whenever there is a new attachment to your source page -- which for us, means every 15 minutes as Step 1 generates a new file that it is attaching to Sheet B.

I now have my Change Ticket sheet (Sheet A) column updating every 15 minutes with the list of Change Request IDs present in my Change Request IDs from my Change Request Sheet (Sheet B).

A nice feature of this solution is that you can add filters on the upload task to only pull the records you want from the attachment on Sheet B. For mine, I only want to show Change Requests that haven't yet been assigned to a Change Ticket, and I also want to exclude anything still under review or new. This way, my dropdown box only shows "valid" change requests that are candidates to attach to a Change Ticket.

The downside of this approach is -- you have to wait 15 minutes because that is the lowest frequency you can run an export task.

I feel like this should have been able to be accomplished "upon update" of a record on Sheet B using DataMesh, but I couldn't get it to work.

If the solution above is the only way to do this right now, then I agree w/ others that it is clunky and SmartSheets should better support this use case.

G

ldubois

Thanks, gjgreetan!

The steps for the offload and upload worked for me perfectly.

Matt Trala

I'm curious why the functionality doesn't exist to just allow within the blank field on the dropdown option to choose a connected reference, just like you can with an empty cell? I can Vlookup fill in the cell data from another sheet on adjacent cells after its filled in, and I can manually copy/paste the list, but just having it fill-in itself seems like a simple function that shouldn't be missing.

Mike matthys

As not everyone is a techie this might be something to consider, its a thirdparty that does it for you

if its only one dropdown hat you want to update then its free, works like a charm

Dropdown Update for Smartsheet (ddupdate.com)

Dawn Armfield

@Mike matthys thank you - this is exactly what I was looking for. I appreciate you posting this. I can second - it works exactly as anticipated.

Mike matthys

@Dawn Armfield can i have a woot woot ? :D

Rsteer

@eric_mixtape: This is a capability that has been requested for YEARS in various discussion threads. As @shschwartz notes, it's a really basic feature of Excel, and I've seen comments that it's also built into Google Sheets. Of course, Smartsheet isn't a spreadsheet, it's a database, but you'd still think it would offer some integration with itself. Instead they'll try to sell users on extra-cost add-ons like Data Uploader (which is really for external data sources, not referencing another sheet) or apparently if you're a professional programmer you can run a program (that seems like it may need to run continuously) to access the Smartsheet API, which might be able to do what Excel does with a simple range reference.

EV_1

https://community.smartsheet.com/discussion/comment/318357#Comment_318357

Hi. I have to agree. Is there any chance you folks can add this functionality to Smartsheet? Thank you.

MBrody

Hi @Andrée Starå,

Can you please share your solution for DDL management via Data Shuttle?

Thanks!

Danielle Wilson

@gjgreetan That was a great explanation! We actually just released an article detailing just how to do this and create Dynamic Dropdowns using Data Shuttle.

@EV_1 If you have a moment to submit an enhancement request for this feature it helps build support for roadmap prioritization!

Best,

Danielle W.

Product Marketing Manager

Smartsheet

kcaudill1989

This would be a great feature to add to the basic features.

For nonprofits it is a budget issue to have to pay additional money for something that is something easy to do in excel.

Darren Mullen

@kcaudill1989 I can understand the budget issue, especially for non profits! However, at this point the options are to either develop your own custom app, purchase Data Shuttle, or another add-on. I like Data Shuttle and have used it for updating dropdowns in the past. Of course, Data Shuttle has many other features as well that may or may not be needed for everyone.

As an alternative, I do have an add-on solution that I offer that doesn't require having an Enterprise license like is the case for Data Shuttle. I'll post my announcement here for anyone else that may be looking for a dropdown update solution as well.

https://youtu.be/sX9rESIi-6o

bryan.krusch

I hope they enable a feature like this soon. It seems a little ridiculous that you need such a robust add-on like data shuttle to complete this task.