Sheet Summary - grabbing Text of the largest entry using COLLECT
Hello all,
I need to be able to search through a list of IDs and grab text from other column(s) associated with the highest entry for the ID.
My ID is made up of two numbers: the first represents the room, and the second is the next sequential number. An example would be 300.5, where it would be Room 300 and the 5th (& highest) entry. I have three columns: the Room is listed separately, the full ID, and then a column with text. I want to create a formula that will scan through the "Room" column to find "300", then search in the full ID column to find the highest number (as there would be 300.1, 300.2, 300.3, ...), and then return the text in the text column. I've done this same process using MAX & Collect to report back dates/values, but I can't figure out the text version of this as MAX only returns numbers or dates. (My formula for that was =MAX(COLLECT([Column with a number]:[Column with a number], Room:Room, "118")) )
I've tried: =INDEX([Text Column]:[Text Column], COLLECT([Text Column]:[Text Column], Room:Room, "300"), 4) which returns a Invalid Data Type error. I'm using "4" as that is the position of the Text Column. Would love some help on this!
Thank you!
Answers
-
Try this...
=INDEX(COLLECT([Text Column]:[Text Column], Room:Room, @cell = 300, [Full ID]:[Full ID], @cell = MAX(COLLECT([Full ID]:[FUll ID], Room:Room, @cell = 300))), 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!