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
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
No problem at all! I'm glad we got there in the end 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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
-
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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!
-
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! 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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
-
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!
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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
-
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)
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
It worked!!!! Thank you so much for helping me through the this. You Rock!
-
No problem at all! I'm glad we got there in the end 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
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?
-
Hiya @TroyT
No problem 🙂
Try this:
=IF(COUNT(CHILDREN()) = (COUNTIF(CHILDREN(), "COMPLETE") + COUNTIF(CHILDREN(), "NOT REQUIRED")), "COMPLETE", "Not Ready")
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!