Formula Help

Andy Been

1000005756.jpg

1000005755.jpg

I have these 2 sheets made in smartsheet in on the sheet named New Clock and Score the Request Number is autogenerated when a form is filled out, and the Kit Name Column is a dropdown of all the rows on the sheet named CS-Kits multiple selections are allowed. I want the request number column on the CS-Kits to autofill based on which kits are selected in the New Clock and Score sheet. Is there a way to accomplish this?

jmyzk_cloudsmart_jp

Hi @Andy Been

Please try this formula for the [Request Number] column;

=JOIN(COLLECT({New Clock and Score Range : Request Number}, {New Clock and Score Range : Kit Name}, CONTAINS([Kit Name]@row, @cell)), CHAR(10))

Explanation

1. COLLECT Function: This part of the formula searches for values in the "Request Number" column of the "New Clock and Score" sheet. It looks through those values where the corresponding "Kit Name" range contains the "Kit Name" from the current row (indicated by @row and @cell ).
2. CONTAINS Function: This is used to check if the "Kit Name" in the current row is within the "Kit Name" range from the sheet.
3. JOIN Function: After collecting all the matching "Request Number" values, the JOIN function combines these values into a single text string. Each value is separated by a line break (CHAR(10) represents a line break in this case).

In summary, this formula retrieves all "Request Number" entries that correspond to the same or similar "Kit Name" in the current row and combines them into a single list, each on a new line. (Please note since the CONTAINS functions syntax is CONTAINS(search_for, range), it returns true if in the case of not an exact match, for example, the serach_for = CS-1 and the range= [CS-1001:CS-1010]. So, use the correct Kit Name in the CS-Kits sheet.)

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

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

jmyzk_cloudsmart_jp

Hi @Andy Been

Please try this formula for the [Request Number] column;

=JOIN(COLLECT({New Clock and Score Range : Request Number}, {New Clock and Score Range : Kit Name}, CONTAINS([Kit Name]@row, @cell)), CHAR(10))

Explanation

1. COLLECT Function: This part of the formula searches for values in the "Request Number" column of the "New Clock and Score" sheet. It looks through those values where the corresponding "Kit Name" range contains the "Kit Name" from the current row (indicated by @row and @cell ).
2. CONTAINS Function: This is used to check if the "Kit Name" in the current row is within the "Kit Name" range from the sheet.
3. JOIN Function: After collecting all the matching "Request Number" values, the JOIN function combines these values into a single text string. Each value is separated by a line break (CHAR(10) represents a line break in this case).

In summary, this formula retrieves all "Request Number" entries that correspond to the same or similar "Kit Name" in the current row and combines them into a single list, each on a new line. (Please note since the CONTAINS functions syntax is CONTAINS(search_for, range), it returns true if in the case of not an exact match, for example, the serach_for = CS-1 and the range= [CS-1001:CS-1010]. So, use the correct Kit Name in the CS-Kits sheet.)

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

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

KPH

A combination of JOIN, COLLECT, and CONTAINS functions should do the trick.

You want to JOIN all the values in the Request Number column in the sheet New Clock and Score, from a

COLLECTion that includes only the rows where the Kit Name column CONTAINS the same value as Kit Name on the current row.

And because each kit number will be on multiple requests, you probably want the output to be in a multi-select drop-down and therefore should use CHAR(10) as the separator for the join.

The formula will look something like this

=JOIN(COLLECT({New Clock Request}, {New Clock Kit}, CONTAINS([Kit Name]@row, @cell)), CHAR(10))

Where the cross sheet reference {New Clock Request} is the Request Number column in the sheet New Clock and Score and {New Clock Kit} is the Kit Name on the sheet New Clock and Score

Andy Been

https://community.smartsheet.com/discussion/comment/448614#Comment_448614

Thanks For the help that worked.

jmyzk_cloudsmart_jp

Happy to help! 😁