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.
A simple function for sure. Smartsheet is not a database, it is a online spreadsheet missing many basic features.
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.
The steps for the offload and upload worked for me perfectly.
©2022. All Rights Reserved Smartsheet Inc.