AI is #NO MATCH for the Smartsheet Community

I've tried the AI feature and other third party AI sites but cannot get this right. I know somebody here can help.

What I am looking for:

On the parent row of a date column called [2B End Date], I want to evaluate the children rows of the hierarchy group, and if the column [Modification Type] (which is a multi-select dropdown column type) has "SCHEDULE" then the formula should check the column called [Modification #] and evalute the order of the numbers, 1,2,3. And where the [Modification Type] HAS SCHEDUDLE AND the [Modification #] is the largest, then return the [2B End Date] from that row (back-up into the parent row).

In the screenshot, there are three children rows - in the second and third the word "SCHEDULE" is in the [Modification Type] column. Therefore, I want to look now at the [Modification #] column and return the [2B End Date Value from that row into the parent row of the same column. In this case, I would expect to see 2/26/27 as the date in the parent row of the [2B End Date] column

Ready

Set

Thanks in advance!

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Try this:

=INDEX(CHILDREN(), MATCH(MAX(COLLECT(CHILDREN([Modification #]@row), CHILDREN([Modification Type]@row), HAS(@cell, "SCHEDULE"))), CHILDREN([Modification #]@row), 0))

Paul Newcome

If we can assume that the numbers would always be in order with 1 as the first child and increasing by one as we go down, we could skip the numbers entirely, COLLECT all dates where the Type HAS "SCHEDULE", and then just pull the last one.

=INDEX(COLLECT(CHILDREN(), CHILDREN([Modification Type]@row), HAS(@cell, "SCHEDULE")), COUNTIFS(CHILDREN([Modification Type]@row), HAS(@cell, "SCHEDULE")))

If the numbers could be in a different order, sorted backwards, etc. and you still want to match on the highest number, you would use a helper column with a VALUE function to remove leading zeros and use that column in the initially suggested formula instead.

All comments

Paul Newcome

Try this:

=INDEX(CHILDREN(), MATCH(MAX(COLLECT(CHILDREN([Modification #]@row), CHILDREN([Modification Type]@row), HAS(@cell, "SCHEDULE"))), CHILDREN([Modification #]@row), 0))

Smar Czar

@Paul Newcome yes, you continue to be "the man". curveball. if the modification # column had values stored as text, rather than value, how would the formula change?

IE. if the children rows had values there of 001, 002, 003, 004 intead of 1,2,3,4. How do the leading 0's alter the formula.

Thanks in advance!

Paul Newcome

=INDEX(COLLECT(CHILDREN(), CHILDREN([Modification Type]@row), HAS(@cell, "SCHEDULE")), COUNTIFS(CHILDREN([Modification Type]@row), HAS(@cell, "SCHEDULE")))

Smar Czar

Thank you. Your first idea is a good one and I hadn't considered. I ultimately went the route of. your second suggestion. Appreciate all of the help.

Humans > AI

Paul Newcome

Happy to help. 👍️

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.