can i use index collect to find a value based on a date?
through use of a form i have a sheet that will create rows with certain departments and issues they've encountered. i have a master sheet that i want to populate with the most recent issue encountered.
My thought is the first find the max date for a department and then use index(collect to collect the first issue that matches that max date. Will this work? when i try i get incorrect argument set..
Best Answer
-
Try putting a comma followed by a one in between the last two closing parenthesis.
), 1)
Answers
-
Can we see a screenshot of your sheet and then your formula?
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with!
-
It should be possible. I have done this plenty of times before. Are you able to provide some screenshots for context?
-
here is my formula
and here are the 2 columns-is the value to index/collect and the other the citeria.
in this caser im looking for the 1st job batch that matches my date
-
Try putting a comma followed by a one in between the last two closing parenthesis.
), 1)
-
IT WORKED!! THANK YOU!
What does the comma, and one represent? when this criteria is true? (just curious)
-
It is the second portion of the INDEX function. The COLLECT function generates a list from the first range based on the rest of the range/criteria sets within it. You then "wrap" the COLLECT function's list in the INDEX function to be able to pull a specific entry from that list. The comma starts the second portion of the INDEX function, and the one tells it to pull the first entry from the list.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!