Assign value from multi-select to row

Alla · 07/24/24

Please help

I have 1 sheet called Project Links Setup (library of unique links and access)

my second sheet has Link Column listing each link 10 times. I want to assign once each access to the Link like this

I think the formula should somehow use distinct and collect, just can figure out that formula. any suggestions are appreciated

jmyzk_cloudsmart_jp · 07/27/24

Hi @Alla

A multiple dropdown list is a text, not a range, so you can not use functions that expect ranges, such as COLLECT, INDEX, etc. So, you need to split the list into multiple columns or cells and use the column or cell range.

Splitting a multiple dropdown list is difficult in Smartsheet as it does not have functions like Excel's SPLITTEXT or Google Sheets' Split function.

https://support.microsoft.com/en-us/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7

https://support.google.com/docs/answer/3094136?hl=en

Since a multiple dropdown list is a text whose elements are delineated by CHAR(10) or Line Feed or New Line, you can use text functions like FIND, LEFT, RIGHT, MID, SUBSTITUTE, etc. to get each element.

https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-functions-49ec57f9-3d5a-44b2-82da-50dded6e4a68

I explained a method using the FIND and MID functions in the following post.

https://community.smartsheet.com/discussion/115582/how-to-parse-out-drop-down-selections-into-separate-cells

The method finds all the CHAR(10) positions and uses the MID function to parse out separate cells. For example, the second element would be a text beggining the second CHAR(10) position + 1 and ending one character before the third CHAR(10) position.

Splitting a Multiple Dropdown List by the cascade of SUBSTITUTE function.

In this example, I used the SUBSITUTE function to parse out each element.

First, I get the first element with the following formula;

[AC1]=IF([Count M]@row > 1, LEFT(Access@row, FIND(CHAR(10), Access@row)), Access@row)

For example, if a text value of a multiple dropdown list is like "Accounting"+CHAR(10)+"Admin"+CHAR(10)+ …, the formula gets the Accounting"+CHAR(10) part. (The IF part checks if multiple elements exist; if not, return the single value.)

Then, using the SUBSTITUTE function, we can use the following formula to get the original text value of a multiple dropdown list minus the AC1 value.

[Access-1]=SUBSTITUTE(Access@row, [AC1]@row, "")

Then, if the CountM value is greater than 2, we apply the LEFT function to the newly gotten value with the following formula;

[AC2]=IF([Count M]@row > 2, LEFT([Access-1]@row, FIND(CHAR(10), [Access-1]@row)), [Access-1]@row)]

As you can see in the image below, especially in rows 1 and 5, the original text value of the multiple dropdown list gets shorter and shorter as you get the AC values successively.

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

Referencing the values from the second sheet.

To reference the values from the second sheet, we can use a formula as follows;

=IFERROR(INDEX({Split Multiple List by Substitute Range AC1:AC6}, MATCH(Link@row, {Split Multiple List by Substitute: Link Name}, 0), [No.]@row), "")

The range {Split Multiple List by Substitute Range AC1:AC6} ranges from the above sheet's AC1 to AC6.

The row index is the MATCH(Link@row, {Split Multiple List by Substitute: Link Name}, 0) part.

For example, the INDEX with the row index returns the following range for the row with Budgeting Guide in the Link Name column.

Accounting Admin Asset Management Property Supervisor REM Director

Then, the [No.]@row is the column index to get each element.

Note the index uses the column index.

FYI, I added helper column NO. as the column index value.

[No.]=IF(MOD([Row ID]@row, 10) = 0, 10, MOD([Row ID]@row, 10))

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

jmyzk_cloudsmart_jp · 07/27/24