Stumped - Index(Collect)

TCJ

Hello, I am stumped on why a formula is not working. The below formula should return the next milestone in a project plan. This uses cross sheet references.

=IFERROR(INDEX(COLLECT({Phase/Milestone}, {IsMilestone}, true, {Start Date}, >Today@row), 1), "")

{Phase/Milestone} is text column of project Phases or Milestones.

{IsMilestone} is a checkbox column when the row is a milestone

Start Date is the Start Date column for the project plan

Today @row refers to a column that uses an automation to update the date in the column to the current day - this column is on the sheet with the formula and the automation runs daily.

The problem is, I get no response. Everything seems to be in order in the sheet - there is text in the Phase/Milestone column, the box is checked in the IsMilestone column and the Today column has has the correct date.

Any suggestions?

Paul Newcome

Happy to help. 👍️

The logic used in your formula won't necessarily return the earliest date. The COLLECT function pulls a list together of all rows that match the range/criteria sets from top to bottom with no special sorting or anything. The INDEX/1 pulls the first entry from the list generated by the COLLECT function.

If you wanted to skip rows where the {Phase/Milestone} is blank, you would add another range/criteria set saying as much.

=IFERROR(INDEX(COLLECT({Phase/Milestone}, {Phase/Milestone}, @cell <> "", ……………………………….

Paul Newcome

What do you get if you remove the IFERROR portion?

TCJ

Oddly, I get nothing. Just a blank cell. I also tried adding test to the IFError ("Error", instead of "").

Paul Newcome

So that means you are getting at least one row found that matches the range/criteria sets of the box being checked and the date being in the future, but the first row that matches that criteria is blank in the {Phase/Milestone} range. Try applying a filter to the source sheet to match the range/criteria sets and see if this is true.

TCJ

Paul, Thank you for your help with this. I appreciated the order of the logic that you bring to this. I have checked all of the columns, and there is text, boxes checked and dates. I have checked this multiple times.

I changed the reference in the Index range (it was looking at Phase/Milestone), so that it was looking at tasks, and it returned a task, though it was not on the row I would have thought, it was a with a start date 1 day later (12/24/24 rather that 12/23/24). I was under the impression that this would always return the first cell that matched the criteria.

TCJ

@Paul Newcome Thank you for your help, it help me finally figure it out!

Paul Newcome

Happy to help. 👍️

The logic used in your formula won't necessarily return the earliest date. The COLLECT function pulls a list together of all rows that match the range/criteria sets from top to bottom with no special sorting or anything. The INDEX/1 pulls the first entry from the list generated by the COLLECT function.

If you wanted to skip rows where the {Phase/Milestone} is blank, you would add another range/criteria set saying as much.

=IFERROR(INDEX(COLLECT({Phase/Milestone}, {Phase/Milestone}, @cell <> "", ……………………………….