Index/Collect with Specific Criteria

CP_123

I am trying to automatically populate the Parent "Content Development Status" column based on the most recent "Child" in the "Tasks" column, but there are certain Children rows in the Task column that I want to ignore. For example, if there is a webinar delivered, I do not want this to populate in the Content Development Status Parent Column, instead I want the most recent Child that is not webinar delivered to be populated.

I created a helper checkbox column that checks a box for all Children rows in the "Task" column that do not equal "Webinar delivered." I have a rather long if/then formula for all of the potential drop down options in the Task column, and it works for those pieces of content that do not have webinar delivery, but when I try it on content that needs the webinar delivered filtered out, it returns "Invalid Value."

Here is one part of the nested If formula that I am currently using:

=IF(INDEX(COLLECT(CHILDREN(Tasks@row), CHILDREN([Not Webinar/Workshop]@row), @cell = 1, CHILDREN(Start@row), MAX(CHILDREN(Start@row))), 1) = "Approved by Compliance", "Approved by Compliance")

Appreciate any insight that you have!

CP_123

That worked - thank you so much!!

Mark.poole

@CP_123

See how this works out for you.

=Index(Collect(Children([Content Development Status]898),MAX(Collect(Children(Start898),Children([Not Webinar/Workshop]898,1)))),1)

CP_123

This came back as Unparseable

Mark.poole

Mark.poole

@CP_123

Alright. Instead of Using INDEX COLLECT. Try an INDEX MATCH.

=INDEX(CHILDREN(Tasks@row), MATCH(MAX(COLLECT(CHILDREN(Start@row), CHILDREN([Not Webinar/Workshop]@row), 1)), CHILDREN(Start@row)))

Below shows the test I created.

CP_123

That worked - thank you so much!!

CP_123

Well, actually it's reading the most recent entry, including "webinar delivered," which I want to ignore, if possible.

Mark.poole

@CP_123

Do you have Webinar Delivered Checked?

my test shows only webinar delivered. When you have the not webinar/workshop checked on a webinar

I assumed you have an if statement in place to auto check. here I removed the auto check. Then checked the box that has Webinar delivered.

CP_123

Nope

Mark.poole

@CP_123

I am unable to replicate the issue. If you like add me on Teams and I can walk you through it.

Mark.Poole@whirlwindsteel.com