How to auto-generate relevant columns based on dropdown list selection　

(This article is for people who like functions. Especially the second half is a list of formulas. If you just want to use this method, there is a dashboard link at the end, so please copy the formula and try it. .)

of the Smartsheet communityunanswered questionAmong them were the following:

there are 4 columns

  • 1 Teacher name (Sets a dropdown that allows multiple selections.)
  • 2 Teacher's email (must be Text/Number, not of type Contact list for another reason)
  • 3 Administrative Assistant Name (text/numeric type)
  • 4 Admin Assistant Email (You can choose either text/number or contact type.)

Is it possible to auto-populate 2-4 columns based on the dropdown selection (multiple selection allowed)?Now I have to add it manually, but 2-4 all depend on #1 choice.

I thought about this automatic input method, so I will explain it below.

Auto-generate relevant columns based on multiple dropdown list selections

Summary of the method

  1. Extract each name from multiple select names in dropdown list
  2. Have a separate list of names and emails, names of relevant people and their emails
  3. Get the email, related person name, related person email based on the name obtained in XNUMX
  4. Summarize the obtained information in one cell
    • For emails simply JOIN
    • For related parties, multiple selected names may have a single related party, so use the DISTINCT function to group only unique information into cells
      • This assumes, for example, that secretary XNUMX is designated as a common secretary for instructor XNUMX and instructor XNUMX.
      • For example, the demo's Look UP list shows instructor Olivia and Liam's common administrative assistant Amelia.

In addition, the table for referring to the name and email of the related person from the selected name is the image below.

Related name, email reference table

How to extract each name from multiple select names in dropdown list

Since the dropdown list is separated by CHAR(10), check its position

  • In the demo example, c1, c2, c3, c4 and 4th places are examined
  • Finding the number of multiple selections in a dropdown list with the COUNTM() function (c#)
  • Use the number of c# to find the values ​​of c1 to c4.　
  • The formula looks like
    • c1 =IF([c#]@row >= 2, FIND(CHAR(10), [Selected names]@row))
    • c2 =IF([c#]@row >= 3, FIND(CHAR(10), [Selected names]@row, [c1]@row + 1))
    • c3 =IF([c#]@row >= 4, FIND(CHAR(10), [Selected names]@row, [c2]@row + 1))
    • c4 =IF([c#]@row >= 5, FIND(CHAR(10), [Selected names]@row, [c3]@row + 1))
  • The meaning of the expression is
    • If the alternative is XNUMX or more, find the position of the first delimiter
    • If there are XNUMX or more alternatives, find the position of the next delimiter starting from the position after the first delimiter
    • For example, if there are 2, XNUMX, XNUMX, etc. options, cXNUMX is obtained by this formula
    • FIND(CHAR(10), [Selected names]@row, [c1]@row + 1))
    • Same for c3 and below

Based on the position information, use the character function (MID () is used here) to extract each name

  • The formula looks like
    • n1: =IF([c#]@row <= 1, [Selected names]@row, LEFT([Selected names]@row, [c1]@row – 1))
    • =IF([c2]@row > [c2]@row, MID([Selected names]@row, [c1]@row + 1, [c1]@row – [c2]@row), IF([ c1]@row < [c2]@row, MID([Selected names]@row, [c1]@row + 1, LEN([Selected names]@row) – [c1]@row + 1)))
    • n3: =IF([c3]@row > [c2]@row, MID([Selected names]@row, [c2]@row + 1, [c3]@row – [c2]@row), IF([ c3]@row < [c2]@row, MID([Selected names]@row, [c2]@row + 1, LEN([Selected names]@row) – [c2]@row + 1)))
    • n4: =IF([c4]@row > [c3]@row, MID([Selected names]@row, [c3]@row + 1, [c4]@row – [c3]@row), IF([ c4]@row < [c3]@row, MID([Selected names]@row, [c3]@row + 1, LEN([Selected names]@row) – [c3]@row + 1)))
  • The meaning of the expression is
    • The option value itself if the option is less than or equal to XNUMX, or to the left of the first delimiter if the option is greater than or equal to XNUMX
    • If you have XNUMX or more options, consider:
      • When c2 > c1 (option XNUMX is followed by option XNUMX and beyond)
        • MID([Selected names]@row, [c1]@row + 1, [c2]@row – [c1]@row)
        • Cut out c1-c2 characters by MID from next location of c1
      • c2
        • MID([Selected names]@row, [c1]@row + 1, LEN([Selected names]@row) – [c1]@row + 1)
        • Cut out from the next location of c1 with MID by adding 1 to the value obtained by subtracting cXNUMX from the length of the cell of choice
      • When c2=c1 (or when c3=c2 in the next cycle)
        • Nothing is done because there is no break

Get email, related person name, related person email based on the acquired name

This is usually brought from another sheet with VLOOKUP (), INDEX (), MATCH ()

Summarize the obtained information in one cell

  • Email
    • Simply connect with JOIN
    • In addition, when displaying with vertical line breaks, use the JOIN(range, CHAR(10)) and CHAR(10) delimiters.
  • In the case of related parties and related party emails
    • As explained above, there may be a case where one common secretary is in charge of several professors and instructors, so only unique values ​​are connected by JOIN with the DISTINCT function.
    • Expression example
    • =JOIN(DISTINCT([rn1]@row:[rn4]@row), CHAR(10))

Summary

This article may have been a little difficult, but I hope you can understand that mastering functions can do a lot of things.

The point is that the multi-select dropdown is delimited by CHAR(10), and the character function is used to systematically calculate the position information to cut out each item of the multi-select.

Please refer tolinkshas a demo dashboard.If you are not good at English,Google TranslatePlease try to make it Japanese.

Auto-generate columns based on drop-down choices – Smartsheet.com

Leave a comment