successions to control yes/no for multiple Rows

TroyT

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))), "")

Capture.JPG

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!

Genevieve P.

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.

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

Genevieve P.

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

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

TroyT

@Genevieve P.

Genevieve P.

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

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

Capture.JPG

Genevieve P.

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

@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

Capture.JPG

!

Best Regards,

Troy

Genevieve P.

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

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

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

@Genevieve P.

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

Genevieve P.

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

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.

Hiya @TroyT

No problem 🙂

Try this:

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