Have you ever hoped you could auto-populate a Smartsheet drop-down list using another Smartsheet source? Do you currently update any drop-down lists manually in Smartsheet to keep the options up to date?
With Smartsheet’s Add-On, Data Shuttle, this process can be automated! We’ve provided written instructions with screenshots along the way to help you streamline this process.
Instructions
In order to auto-populate a drop-down list, you will need two Data Shuttle workflows: (1) Offload Data and (2) Upload Data. The Offload Data workflow will automatically download a csv file from one of your sheets and attach it to any sheet you’d like. The Upload Data workflow allows you to take the information in that excel document to populate any drop-down field of your choosing.
Offload Workflow:
When you get into Data Shuttle, you click on the plus sign on the left-hand bar, and select Offload Data:
Data Shuttle will walk you through the configuration:
Source: Select the sheet that has the rows of data you would like to auto-populate your drop-down list from.
For Target: Select the target location as a Smartsheet attachment and select the sheet you would like the data to be sent to. You will also select a file name and type at this step
Filters: If there are any criteria for you to filter out specific rows, this is your opportunity to do that.
For example, if you’re working toward auto-populating a list of Project Names – you may be taking your source data from your Intake Sheet. In this use case, you likely only want to pull over approved projects into the drop-down list. This can be set in filters on your offload workflow.
Mapping: For an offload workflow, this simply confirms the headings of the Smartsheet that will fill into excel. They can also be re-ordered here, but for this use case – it is not necessary.
Run Options:
Manual: You can log in to Data Shuttle and push the button to ‘run’ whenever you’d like.
On Schedule: You can select a schedule of when you’d like the workflow to run. (For example: every day, every week, or even several times a day.)
Finally, you name your workflow and it will be all set to go! Before moving to the Upload Data workflow, it is best to run the workflow manually once, test that the data made it over and everything looks as you’d expect. You’ll be looking to confirm a CSV excel has been uploaded to the target sheet.
Upload Workflow:
When you get into Data Shuttle, you click on the plus sign on the left-hand bar and select Upload Data. It will show all the same options as the first workflow.
Source:
For source, you will select ‘Smartsheet Attachment’ as Source Location and select the same sheet that was chosen for your Target location in the Offload Workflow.
The attachment will also have to be selected by Most Recent or Name. I recommend using name as the source sheet could have several attachment types. The name selected in the previous workflow will be noted here. (TIP: Make sure to include ‘.CSV’ or whichever file type you selected.
Finally, you will select which row is identified as the header in your file upload. In this use case, it will be 1.
Target: Select the same sheet as the Source sheet and the workflow action will be ‘Update dropdown choices for selected columns.
Filters: Filters can be selected on this end as well, but as they were set in the Offload Workflow, it won’t be necessary here.
Mapping: Search on the right-hand side for the column you would like to populate the drop-down fields for. In the corresponding drop-down to the left, select the column of data to pull from in the excel document.
Run Options: Select ‘run on attachment’ – the schedule is set in the Offload Workflow.
Expressions: No changes here.
Name your workflow and you are ready to run and test!
Want to learn more about Smartsheet, Data Shuttle, and Echo's work? Head over to our Smartsheet Page or set up a meeting with one of our experts!