Autopopulate 3 columns based on a drop down choice in the 4th

OshaK

Hello,

I have 4 columns

1. Faculty name (set drop down with multiple choices)
2. Faculty email (has to be Text/Number, not a Contact list type for another reason)
3. Admin assistant name (Text/Number type)
4. Admin assistant email (can be either Text/Number or Contact type)

Is it possible to auto populate 2-4 columns based on the drop-down selection (with multiple choices). Right now they have to be added manually, but 2-4 all depend on the #1 choice.

thank you

J Tech

Hi Osha,

Yes, it's possible to auto-populate columns based on the drop-down selection in Smartsheet. You can try this:

1. Create a new column for each field you want to auto-populate (Admin Assistant Name, Admin Assistant Email). You can leave the Faculty Email column as it is.
2. In the Admin Assistant Name column, you can use an IF statement to check the value of the Faculty Name column and populate the Admin Assistant Name accordingly. Here's an example formula:
3. =IF([Faculty Name] = "John Doe", "Jane Smith", IF([Faculty Name] = "Jane Smith", "John Doe", ""))
4. This formula checks if the Faculty Name column equals "John Doe" or "Jane Smith". If it does, it populates the Admin Assistant Name column with the corresponding name. Otherwise, it leaves the column blank.
5. In the Admin Assistant Email column, you can use a similar IF statement to check the value of the Faculty Name column and populate the Admin Assistant Email accordingly. Here's an example formula:
6. =IF([Faculty Name] = "John Doe", "jane.smith@email.com", IF([Faculty Name] = "Jane Smith", "john.doe@email.com", ""))
7. This formula checks if the Faculty Name column equals "John Doe" or "Jane Smith". If it does, it populates the Admin Assistant Email column with the corresponding email address. Otherwise, it leaves the column blank.
8. Note: If you want to use a Contact list for the Admin Assistant Email column, you can simply reference the Contact list column in the IF statement instead of using an email address.

jmyzk_cloudsmart_jp

The following is the method I use for similar situations.

1. Cut out each name from the multiple-choice names in the drop-down list cell using the position of CHAR(10).
2. Separately prepare a list of names and emails, the names of related persons, and their emails.
3. Retrieve emails, related persons' names, and associated emails based on the names retrieved in 1 using VlOOKUP or INDEX, MATCH.
4. Combine the retrieved information in a single cell with JOIN.

The published dashboard in the URL below contains demo sheets, method descriptions, and a column formulas list.

https://app.smartsheet.com/b/publish?EQBCT=af9dd1648f7a458cacb04e2563f236ab

jmyzk_cloudsmart_jp

First, CHAR(10) is a selection divider in a multiple-selection dropdown cell.

If you select two names, "Jonny Depp" and "Winona Ryder," for example, the multiple-selection dropdown cell is something like 

Jonny DeppCHAR(10)Winona Ryder

Second, c1 or c2 is my shorthand for the first and second CHAR(10) positions.

c1's formula is  =IF([c#]@row >= 2, FIND(CHAR(10), [Selected names]@row))

or =IF([c#]@row >= 2, FIND(CHAR(10), [OHNS Faculty Moderator]@row))

in your case.

What the formula means is;

if the number of selections (c#: =COUNTM([Selected names]@row)) is more than two, find the position of CHAR(10) in the multiple-selection dropdown cell and return the position.

In the example above, since "Jonny Depp" has ten words, including space, the formula returns 11 as c1 or the first CHAR(10) position.

But if you only select one name, there is no CHAR(10) in the multiple-selection dropdown list cell, so the formula does nothing. 

(IF([c#]@row >= 2 is False)

That is why c1 is not returning anything.

For your information, I re-examined, and c0 is not necessary.

J Tech

Hi Osha,

Yes, it's possible to auto-populate columns based on the drop-down selection in Smartsheet. You can try this:

1. Create a new column for each field you want to auto-populate (Admin Assistant Name, Admin Assistant Email). You can leave the Faculty Email column as it is.
2. In the Admin Assistant Name column, you can use an IF statement to check the value of the Faculty Name column and populate the Admin Assistant Name accordingly. Here's an example formula:
3. =IF([Faculty Name] = "John Doe", "Jane Smith", IF([Faculty Name] = "Jane Smith", "John Doe", ""))
4. This formula checks if the Faculty Name column equals "John Doe" or "Jane Smith". If it does, it populates the Admin Assistant Name column with the corresponding name. Otherwise, it leaves the column blank.
5. In the Admin Assistant Email column, you can use a similar IF statement to check the value of the Faculty Name column and populate the Admin Assistant Email accordingly. Here's an example formula:
6. =IF([Faculty Name] = "John Doe", "jane.smith@email.com", IF([Faculty Name] = "Jane Smith", "john.doe@email.com", ""))
7. This formula checks if the Faculty Name column equals "John Doe" or "Jane Smith". If it does, it populates the Admin Assistant Email column with the corresponding email address. Otherwise, it leaves the column blank.
8. Note: If you want to use a Contact list for the Admin Assistant Email column, you can simply reference the Contact list column in the IF statement instead of using an email address.

jmyzk_cloudsmart_jp

The following is the method I use for similar situations.

1. Cut out each name from the multiple-choice names in the drop-down list cell using the position of CHAR(10).
2. Separately prepare a list of names and emails, the names of related persons, and their emails.
3. Retrieve emails, related persons' names, and associated emails based on the names retrieved in 1 using VlOOKUP or INDEX, MATCH.
4. Combine the retrieved information in a single cell with JOIN.

The published dashboard in the URL below contains demo sheets, method descriptions, and a column formulas list.

https://app.smartsheet.com/b/publish?EQBCT=af9dd1648f7a458cacb04e2563f236ab

OshaK

@J Tech and @jmyzk_cloudsmart_jp

yay, thank you sooo MUCH for such a detailed answer! I'm just learning the formulas so this it immensely helpful! thank you so much!

@jmyzk_cloudsmart_jp

I'm setting it up according to your instructions, but can you please let me know what i'm doing wrong? If i have one person selected in dropdown - c1 is not returning anything, but if i have 2 - I see it calculates '12'.

I'm copy/pasting formulas from your demo sheet (again, Thank You So Much!)

Why is it happening?

jmyzk_cloudsmart_jp

First, CHAR(10) is a selection divider in a multiple-selection dropdown cell.

If you select two names, "Jonny Depp" and "Winona Ryder," for example, the multiple-selection dropdown cell is something like 

Jonny DeppCHAR(10)Winona Ryder

Second, c1 or c2 is my shorthand for the first and second CHAR(10) positions.

c1's formula is  =IF([c#]@row >= 2, FIND(CHAR(10), [Selected names]@row))

or =IF([c#]@row >= 2, FIND(CHAR(10), [OHNS Faculty Moderator]@row))

in your case.

What the formula means is;

if the number of selections (c#: =COUNTM([Selected names]@row)) is more than two, find the position of CHAR(10) in the multiple-selection dropdown cell and return the position.

In the example above, since "Jonny Depp" has ten words, including space, the formula returns 11 as c1 or the first CHAR(10) position.

But if you only select one name, there is no CHAR(10) in the multiple-selection dropdown list cell, so the formula does nothing. 

(IF([c#]@row >= 2 is False)

That is why c1 is not returning anything.

For your information, I re-examined, and c0 is not necessary.