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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!