INDEX COLLECT with MAX and ISBLANK
Hi folks, I'm a bit stuck with this formula. Hope someone can help.
I want the Link to Latest Edit column to pick up the Latest Edit for whichever project is indexed in the row. The Current formula is leveraging my Autonumber column and picking up the last added with a MAX.
Like so:
This all works fine except that sometimes my users forget to log their latest edit and that field is left empty. In this case I want the formula to look for the last one added for that project, basically the MAX where Latest Edit is not blank.
I tried to drop a second criteria into the COLLECT to NOT Collect if the Latest Edit field is blank – presuming that the formula would then look to the MAX that was not blank.
Alas, I can’t seem to get it right. Where am I going wrong? Any advice?
Thank you.
Best Answer
-
What number do you get when you do just the MAX/COLLECT?
Answers
-
Try adding the "not blank" range/criteria set to the MAX/COLLECT as well.
-
Thanks so much @Paul Newcome But I'm getting an #INVALID VALUE.
It also occurred to me that I might need some kind of IFERROR in case a latest Edit hasn't been added at any point for a given project. Any advice you can provide is gratefully received.
-
What number do you get when you do just the MAX/COLLECT?
-
You set me on the right path here, @Paul Newcome.
I broke a MAX/COLLECT into a helper column [max not blank] like so.
=MAX(COLLECT(AutoNum:AutoNum, Project:Project, Project@row, [Latest Edit]:[Latest Edit], <>""))
Then in my destination column I got:
=IF(COUNTIFS([Latest Edit]:[Latest Edit], <>"", Project:Project, Project@row) < 1, "No Link Shared Yet", JOIN(COLLECT([Latest Edit]:[Latest Edit], AutoNum:AutoNum, [max not blank]@row)))
I could probably try and smash those together at some point but it's working as expected for now. Thank you for your help. All the best.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!