Formula Help
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?
Best Answer
-
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
- 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 ).
- CONTAINS Function: This is used to check if the "Kit Name" in the current row is within the "Kit Name" range from the sheet.
- 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.)
Answers
-
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
- 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 ).
- CONTAINS Function: This is used to check if the "Kit Name" in the current row is within the "Kit Name" range from the sheet.
- 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.)
-
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
-
Thanks For the help that worked.
-
Happy to help! 😁
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 143 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
