Index/Join/Collect - Question
Hello,
I have two sheets I am trying to reference together and I am having a bit of trouble.
I have one cell in a sheet with row numbers (i.e. 1,2,3,4,5) and another sheet that based on those numbers gives a control. I am trying to take the cell with the list of numbers and instead simply display the control name so we don't have to jump between two sheets. I can make INDEX work to grab one but cant for the life of me use a function to go through all the numbers and grab their associated controls.
Example of the control list
Example of main sheet
I would love to be able to add a list of numbers to the above cell (15, 16, 17) and instead have it show the control names (Master Keys, Redundant Components, Software repository, etc.)
Thank you!
Z
Answers
-
You would need a JOIN/COLLECT combo instead of an INDEX function, and you would need to make the column you are putting numbers into a multi-select dropdown type column.
-
Apologize, still a bit new to this whole side. Still running stuck, Now I am getting a blank cell.
Thanks a ton Paul.
-
You will also need a HAS function for [Column3].
=JOIN(COLLECT({Range 1}, {Range 3}, HAS([Column3]@row, @cell)), " - ")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!