INDEX COLLECT with MAX and ISBLANK

Jim B

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.

Paul Newcome

What number do you get when you do just the MAX/COLLECT?

Paul Newcome

Try adding the "not blank" range/criteria set to the MAX/COLLECT as well.

Jim B

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.

Paul Newcome

What number do you get when you do just the MAX/COLLECT?

Jim B

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.

Paul Newcome

Happy to help. 👍️