Cross reference a sheet and return multiple values based on selection
looking for some formula help .....i have a working sheet (#1) that when selecting one or multiple [Suggested Remedy Code]@row that it will search my reference sheet (#2) for the correlating {Result Code} and populate with all the {Description} associate with the respective codes in my sheet 1 column [Corrective Action] ---i have been playing with index, collect and has and simply stumped....
Best Answer
-
Give this a try. You'll need to replace the column name with the reference name where applicable.
=JOIN(COLLECT(Description:Description, [Result Code]:[Result Code], HAS([Suggested Remedy Code]@row, @cell)), " - ")
Answers
-
Give this a try. You'll need to replace the column name with the reference name where applicable.
=JOIN(COLLECT(Description:Description, [Result Code]:[Result Code], HAS([Suggested Remedy Code]@row, @cell)), " - ")
-
Just wanted so say "THANK YOU!" I am but a novice and tried to figure this out on my own. Do you have any suggestions on how I can level up with any resources/authors on how to learn and build writing formulas? Just to let you know I had to make a minor adjustment and it worked. This is what I had to ultimately tweak: =JOIN(COLLECT({Corrective Description}, {Result Code}, HAS([Suggested Remedy Code]@row, @cell)), "-")
-
Review the function list and the examples each one has. https://help.smartsheet.com/functions
Lurk the discussions. Most of the questions people come here with questions that have already been answered somewhere in the past. Smartsheet University is a good starting point that I recommend everyone at least takes a look at even if you don't want do all the lessons. A lot of the knowledge around Smartsheet just comes down to knowing whether or not Smartsheet is capable of accomplishing what you are trying to do so a quick review can go a long way. Lastly, helping people in these discussions is a great way to expand your understanding. Even if you can't answer them it's good to try and then when someone else posts the answer you get to learn how they did it. Try not to just write out formulas in the discussion but actually build the sheet and see it work yourself.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!