successions to control yes/no for multiple Rows

The formula I have came from Smartsheet support, and it got me close but not there yet.

=IFERROR(IF(AND(CONTAINS(row@row, Successors:Successors), HAS([Overall Status]:[Overall Status], "COMPLETE")), 1, OR(=IF(AND(HAS(Successors:Successors, row@row), HAS([Overall Status]:[Overall Status], "COMPLETE")), 1))), "")

I am trying to use predecessors in the traditional way, however I would like to have the "Overall Status" of "COMPLETE" trigger multiple y/n's in multiple rows under the "Next Task Ready" column. is this possible? Currently the forumla selects lots of rows, but not the ones i want.


In the screenshot shown, i have row 3 complete, which the predecessors should trigger rows 9, 33, and 45 to check yes, but tons of them are checked yes and i have no idea why. Please help!

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @TroyT

    Your first statement is looking to see if the Successors column contains the current row number, and if anywhere in the column there's a "Complete" value. These aren't identified as needing to be in the same row, which is why all of your checkboxes are being checked.

    To make sure you're looking for when "Complete" exists in the same row as the current row's number in the Successors column, I would use a COUNTIFS, like so:

    =IF(COUNTIFS(Successors:Successors, CONTAINS(Row@row, @cell), [Overall Status]:[Overall Status], "COMPLETE") > 0, 1)

    It sounds like you'll want the current row to UN-check once this row is Complete, is that correct? In this case, we can add another IF statement back in to first check the current row status:

    =IF([Overall Status]@row = "COMPLETE", 0, IF(COUNTIFS(Successors:Successors, CONTAINS(Row@row, @cell), [Overall Status]:[Overall Status], "COMPLETE") > 0, 1))

    That way it will un-check as you progress through the sheet.

    However keep in mind that the CONTAINS function is looking for partial matches as well. This means that if a row's successor contains row number 2 (so if it says 12, or 21) then this will check off row 2 as well:

    I hope this helps! There's another thread around the SUCCESSOR function here that you may be interested in.

    Cheers,

    Genevieve

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    No problem at all! I'm glad we got there in the end 🙂

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @TroyT

    Your first statement is looking to see if the Successors column contains the current row number, and if anywhere in the column there's a "Complete" value. These aren't identified as needing to be in the same row, which is why all of your checkboxes are being checked.

    To make sure you're looking for when "Complete" exists in the same row as the current row's number in the Successors column, I would use a COUNTIFS, like so:

    =IF(COUNTIFS(Successors:Successors, CONTAINS(Row@row, @cell), [Overall Status]:[Overall Status], "COMPLETE") > 0, 1)

    It sounds like you'll want the current row to UN-check once this row is Complete, is that correct? In this case, we can add another IF statement back in to first check the current row status:

    =IF([Overall Status]@row = "COMPLETE", 0, IF(COUNTIFS(Successors:Successors, CONTAINS(Row@row, @cell), [Overall Status]:[Overall Status], "COMPLETE") > 0, 1))

    That way it will un-check as you progress through the sheet.

    However keep in mind that the CONTAINS function is looking for partial matches as well. This means that if a row's successor contains row number 2 (so if it says 12, or 21) then this will check off row 2 as well:

    I hope this helps! There's another thread around the SUCCESSOR function here that you may be interested in.

    Cheers,

    Genevieve

  • TroyT
    TroyT ✭✭
    edited 12/08/22

    Genevieve,

    Thank you for such a helpful and thorough response! I learned so much from your explanation. Quick follow up question, I now understand how the CONTAINS function is adding in additional yes/no, is there another function I can use besides contains that is looking at the exact number? Thanks again!!!

    Best Regards,

    Troy

  • Genevieve P.
    Genevieve P. Employee Admin

    Hey @TroyT

    Good question, I've been thinking about this.

    The short answer is no, if we're looking in a text/number column like the Predecessor column then we can't parse out the exact number.

    However! If you're willing to add yet another helper column into your sheet, I believe we can use a formula to translate the numbers into multi-select values. This will separate out the values in a way that we can then use the HAS function to search for a single, individual selection within multiple selections.

    Formula to convert numbers to multi-select:

    =SUBSTITUTE(Successors@row, ",", CHAR(10))

    The CHAR(10) is a line break. The Substitute formula will replace every instance of "," or a comma with a multi-select break.


    Adjusted "Next Due" formula, using HAS instead of CONTAINS and looking at the helper column:

    =IF([Overall Status]@row = "COMPLETE", 0, IF(COUNTIFS(MultiSelect:MultiSelect, HAS(@cell, Row@row), [Overall Status]:[Overall Status], "COMPLETE") > 0, 1))

    Note that the HAS function has the opposite structure to CONTAINS - you'll want to state "@cell" before the "Row@row" reference.

    Let me know if that works for you!

    Cheers,

    Genevieve

  • TroyT
    TroyT ✭✭

    @Genevieve P.

    Thank you again for your thoughtfulness! What Column type should i select, currently i have "text/number", and it seems to be displaying the numbers similar to your example however the "Next Task Ready" formula doesnt seem to be returning any checks, see attached.

    To Clarify, i am using the following two formulas, the first for Multiselect, the second for Next Task Ready:

    =SUBSTITUTE(Successors@row, ",", CHAR(10))

    =IF([Overall Status]@row = "COMPLETE", 0, IF(COUNTIFS(MultiSelect:MultiSelect, HAS(@cell, row@row), [Overall Status]:[Overall Status], "COMPLETE") > 1, 0))

    Thanks again for your responses, you are awesome!


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @TroyT

    Very close!!

    Adjust your current "MultiSelect" column to be a Dropdown list type of column with "Allow multiple values" toggled on:

    That should do the trick! 🙂

  • TroyT
    TroyT ✭✭

    @Genevieve P.

    That did adjust the MultiSelect to show individual number, but I am not getting the y/n to trigger correctly under "Next Task Ready" like you did in your above screenshot. for example, when the first row is complete, i expect rows 16 and 22 to trigger y/n, however only 28 and 35 turns on? I thought i understood, now i am confused again lol. thanks again for the prompt responses

    !

    Best Regards,

    Troy

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @TroyT

    Can I confirm what formula you have in the checkbox column?

    In mine, I have if the COUNT is greater than 0, check the box:

    =IF([Overall Status]@row = "COMPLETE", 0, IF(COUNTIFS(MultiSelect:MultiSelect, HAS(@cell, Row@row), [Overall Status]:[Overall Status], "COMPLETE") > 0, 1))

    It looks like you may have the 1 and the 0 swapped around. Let me know if that solved it!

  • TroyT
    TroyT ✭✭

    @Genevieve P.

    I thought i had the 0 and 1 mixed up too, but i dont think so... For the checkbox i am using:

    =IF([Overall Status]@row = "COMPLETE", 0, IF(COUNTIFS(MultiSelect:MultiSelect, HAS(@cell, row@row), [Overall Status]:[Overall Status], "COMPLETE") > 0, 1))

    looks identical. would you like to peak at the actual Ssheet?

    Best Regards,

    Troy

  • Genevieve P.
    Genevieve P. Employee Admin

    Hey @TroyT

    You're right! It looks good. I took a peek back up at your first screen capture and noticed that the Row Number in the column title "Row" is different than the actual row number. The COUNTIFS function is basing the row number off of that cell, which is why the wrong rows are being checked.

    How are you populating those first two helper columns?

    I assumed that the column "nrow" was an auto-number column, but now I see the blank cells and realize it's not. What I would do is create a new auto-number column, titled anything. In my example I'll call it "Auto".

    Then in your Row column, use the MATCH function to return the row's number:

    =MATCH(Auto@row, Auto:Auto, 0)

  • TroyT
    TroyT ✭✭

    @Genevieve P.

    It worked!!!! Thank you so much for helping me through the this. You Rock!

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    No problem at all! I'm glad we got there in the end 🙂

  • TroyT
    TroyT ✭✭

    @Genevieve P.

    Follow up question, different subject but same sheet:

    i am currently using the following formula to control a parent reporting "Ready" or "Not Ready":

    =IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "COMPLETE"), "COMPLETE", "Not Ready")

    I would like to add an "OR" function to this to allow both "COMPLETE" and "NOT REQUIRED" to trigger the parent "Ready". Can you help me understand how to insert the "OR" function?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hiya @TroyT

    No problem 🙂

    Try this:

    =IF(COUNT(CHILDREN()) = (COUNTIF(CHILDREN(), "COMPLETE") + COUNTIF(CHILDREN(), "NOT REQUIRED")), "COMPLETE", "Not Ready")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!