How do you automatically populate columns from a master sheet if a specific item is selected?

Pops1988

Hi All,

I'm building a database of products and would like to automatically populate different data when a specific product is chosen from a drop-down column. For example:

Column A > Product Column: Select "Software A"

-automatically populates-

Column B > Marketing Manager: John Smith

Column C > Product Manager: Sally Jones

Column D > Support Manager: Alex Nahn

Would I first create a master sheet that maps all of the data, then create another sheet with a 'vLookup' type of function? Is this possible with SmartSheet? Thanks!

Darren Mullen

@Pops1988 Yes, this is possible. And you are on the right track with creating a master sheet, then you could use vLookup if you data is organized properly, otherwise using Index() and Match() together will bring in the data you want. You just need to make sure each item in your master database has a unique identifier that can be used to choose the item in the destination sheet.

Jeff Reisman

@Pops1988

You could accomplish this with a series of Workflow Automations, depending on how many products you have; alternatively, you could use a lookup sheet and INDEX/MATCH formulas to populate those values. (In my opinion, INDEX/MATCH is superior to vlookup because columns can be moved around, or have columns inserted between them, without messing up the formula.) Keep in mind, while automations may take a minute or two (or three) to populate the sheet, overall they are far less resource intensive than cross-sheet formulas and will keep your sheet from bogging down as your row count grows.

For Automations, you could create a "Change a Cell" automation for each of the three columns you want to populate automatically. Here are the steps to create your first one to set the Marketing Manager.

Click on Automation at the top left, select Create workflow from template.

In the template gallery, scroll down to the "Sheet Changes" section and select "Change a cell value when specified criteria are met." Read about the workflow and then click "Use Template."

Add a workflow title.

For trigger, select 'When rows are added or changed', 'When Product changes to: Any value', and Run workflow: When triggered.

Add a condition: Where Product is one of (select all of the products where John Smith should be the marketing manager.)

Under Change cell value, select your Marketing Manager column and set the value you want populated: John Smith

Save the workflow. Go back to your sheet grid and test the workflow by creating a new row with one of John's products selected. If it works, come back to the workflow by clicking on Automation-> Manage workflows. Edit your workflow.

Click the blue circle "+" sign above the condition block and select "Add a condition path." A new condition block will appear to the right. Again, set the condition of "Where Product is one of" and select the set of products that match another marketing manager. Below this new condition block, add a Change a cell value action, select your Marketing Manager column, and set the value you want populated for this next set of products.

Rinse and repeat!

Pops1988

This is amazing, thank you so much for this!

One quick follow up, for the PRODUCT column (which will act as a trigger for all of the workflows), how would I populate a list that is pulled from the master data sheet (Product Column)? I know I can just add the products as drop downs, but ideally, I would like to pull that list from the Master sheet, in case any changes occur.