INDEX(COLLECT) function problem?
I am using an INDEX(COLLECT) function to return an amended date by family name. It works well except in the event a family was amended twice. It is returning the first amended value as opposed to the latest amended value. How can I get Smith to show up as Aug-27-2021 in Column 5? When I change row index to 2 it works for Williams but not for the others, so I am unsure how to "COLLECT" the latest revision. This is my Formula:
=IFERROR(IF(Entry@row = "Amended", "", INDEX(COLLECT([Revised Date]:[Revised Date], [Family Name]:[Family Name], [Family Name]@row, Entry:Entry, "Amended"), 1)), "")
Thanks in advance!
Susan
Best Answer
-
Hello @Susan Vieira
MAX(COLLECT) should work for you
=IFERROR(IF(Entry@row = "Amended", "", INDEX(MAX(COLLECT([Revised Date]:[Revised Date], [Family Name]:[Family Name], [Family Name]@row)), 1)), "")
cheers
Answers
-
Hello @Susan Vieira
MAX(COLLECT) should work for you
=IFERROR(IF(Entry@row = "Amended", "", INDEX(MAX(COLLECT([Revised Date]:[Revised Date], [Family Name]:[Family Name], [Family Name]@row)), 1)), "")
cheers
-
Perfect! I use INDEX/COLLECT so infrequently that I didn't even realize I could use MAX in there! Thank you very much.
-
@Kelly Moore , I've encounter a hitch with this formula. It returns the latest date but if that date happens to be before the original, it will not. It returns the literal latest date. But I need it to return the date in the latest amendment, regardless if it is before or after the original.
How can I leverage my formula to return the Revised date of the latest Amendment? Not necessarily the latest date? Does this make sense?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 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
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!