Index/Collect with Specific Criteria

Options

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!

Best Answer

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    @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)

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • CP_123
    CP_123
    edited 06/11/24
    Options

    This came back as Unparseable

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/12/24
    Options

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/12/24
    Options

    @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.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • CP_123
    CP_123
    Answer ✓
    Options

    That worked - thank you so much!!

  • CP_123
    Options

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

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/14/24
    Options

    @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.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/14/24
    Options

    @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

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!