Need to return a value with INDEX(COLLECT) but not if cell is BLANK

Susan Vieira

Hello,

I am unsure if what I am doing is possible or not but I need to return a value with specific criteria. I need the latest "Revised Date" from the most recent amended version (Occurrence). However, it is possible the latest amended has no updated "Revised Date" field so it should keep the previous revised date..

In my example for Felx, the New/Final End Date should still be 8-Sep-2021.

Here is my formula:

=IFERROR(IF(Entry@row = "Amended", "", INDEX(COLLECT([Revised Date]:[Revised Date], [Revised Date]:[Revised Date], NOT(ISBLANK(@cell)), [Family Name]:[Family Name], [Family Name]@row, Occurence:Occurence, MAX(COLLECT(Occurence:Occurence, [Family Name]:[Family Name], [Family Name]@row))), 1)), Date@row)

So I'm trying to collect the date from the latest amendment (number in occurrence column) but only if there is a date in the revised date cell. Where am I going wrong with this?

Thank so much for your help.

Susan Vieira

@Paul Newcome I GOT IT! I tweaked the helper column and got it to return the right amendment!

=IF(Entry@row = "Original", MAX(COLLECT(Occurence:Occurence, [Family Name]:[Family Name], [Family Name]@row, [Revised Date]:[Revised Date], NOT(ISBLANK(@cell)))))

This is giving me what I need!

Thanks so much for heling me break it down. :)

Paul Newcome

Is the screenshot how it SHOULD look in the red column with manually entered dates? If not, are you able to provide some samples of what should be populated and where?

Susan Vieira

@Paul Newcome In the screenshot, everything is how it should look except Felx. The wrong date is returned. My formula above is in the red column. I want to see the latest Revised Date in the red column for every Original row (Entry). So basically, per family name, I want it to check all my amendments, and return the latest date. Every date/Family in my example above works Felx, and that is because the latest amendment had no revised date.

The Smith example is an example of it working properly, returns the latest amended date. I run into a problem when the latest amendments has a blank revised date.

It worked perfectly when I did not have any blanks in the Revised Date cell in any Amended row. But it is very possible that some Amended entries will not have a date there (as in my Felx example), so I need the formula to be able to return the most recently Amended revised date entry.

I hope this makes sense.

Paul Newcome

Ok. I am also kind of curious about your existing formula... Usually have a COLLECT function inside of a COLLECT function throws a #NESTED CRITERIA error, but it doesn't look like that is the case for you?

My suggestion would be to insert a Helper Column (which can later be hidden to keep the sheet looking cleaner) that has

=IF(Entry@row = "Original", MAX(COLLECT(Occurence:Occurence, [Family Name]:[Family Name], @cell = [Family Name]@row)))

Then in the date column you would use something along the lines of...

=IF(Entry@row = "Original", IF(COUNTIFS([Family Name]:[Family Name], @cell = [Family Name]@row, Entry:Entry, @cell = "Amended") = 0, Date@row, INDEX(COLLECT([Revised Date]:[Revised Date], Occurence:Occurence, @cell = [Helper Column]@row, [Family Name]:[Family Name], @cell = [Family Name]@row), 1)))

Susan Vieira

@Paul Newcome , no it is not giving me an error with the 2 COLLECT functions, perhaps because I am "collecting" from 2 different ranges?

I tried your solution which was doing a similar thing as my formula and giving me the same wrong result. For Felx it is returning a blank date because the revised date of Amendment 2 is blank. I need for it to return the date from the last amendment that had a date. So in the case of Felx, it would be amendment 1. In the helper column, how to I build the formula to return the latest updated date? For the example of Felx, I need it to return 8-Sep-2021, which was modified in amendment 1. See below, helper column is Column12. Helper column is on the right track but I need it to return the latest amendment number that has a date, not simply the latest amendment.

I basically want it to look at revised dates for any amendments for a particular family, and return the latest one, thereby, ignoring any amendments that have a blank in Revised date. I know in my head what I need but for the life of me, I can't translate it to a formula. Thank so much, I appreciate your time!!

Susan Vieira

@Paul Newcome I GOT IT! I tweaked the helper column and got it to return the right amendment!

=IF(Entry@row = "Original", MAX(COLLECT(Occurence:Occurence, [Family Name]:[Family Name], [Family Name]@row, [Revised Date]:[Revised Date], NOT(ISBLANK(@cell)))))

This is giving me what I need!

Thanks so much for heling me break it down. :)

Paul Newcome

I tested it with two different ranges and still got the error.

And yes. I did forget to include the "not blank" portion. My apologies. Glad you were able to get it figured out.