Stumped - Index(Collect)

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?

Find more posts tagged with

Accepted answers

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 <> "", ……………………………….

All comments

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. 👍️

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 <> "", ……………………………….

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.