Check a checkbox with 2 criteria
I have 3 columns:
- Action Plan
- Move Row (checkbox)
- Helper
I need to check the box if the following is met:
- 2 exact values in the helper column.
- If the Action Plan is empty.
- Check the checkbox in the row with the empty Action Plan.
I keep getting an error using this formula if the Action Plan is empty (2nd line):
=IF(COUNTIFS([Action Plan]@row, ISBLANK(@cell)), IF(COUNTIFS(Helper:Helper, Helper@row) > 1, 1))
If not, it is fine (1st line).
Thanks in advance.
Best Answer
-
How is your "Month" column being populated?
Formula errors are like dominoes - if there is one cell with an error in a range and then you reference it in a different formula, the original error will be displayed in your second formula. I'm curious to see if the error you have right now is actually an error from a different formula. That's where the IFERROR will help... if there are any errors in your JOIN formula, it will prevent this from affecting your IF(AND formula. Does that make sense?
Can you try updating the JOIN formula just in case?
=IFERROR(JOIN(Month@row:Team@row), "")
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Can you try below formula?
=IF(AND(ISBLANK([Action Plan]@row), COUNTIFS(Helper:Helper, Helper@row) > 1), 1, 0)
Best Regards
Amit Wadhwani, Smartsheet CoE, Ignatiuz Software, Exton, PA
Best Regards
Amit Wadhwani, Smartsheet Community Champion
Smartsheet CoE, Ignatiuz, Inc., Exton, PA
Did this answer help you? Show some love by marking this answer as "Insightful 💡" or "Awesome ❤️" and "Vote Up ⬆️"https://www.linkedin.com/in/amitinddr/ -
I am still getting an the following:
-
I see that you have a formula in your Helper column. Is it possible that this helper column has an error anywhere in it? If so, since you're referencing the entire column in your current formula, it will present an error.
Try wrapping an IFERROR statement around your Helper formula:
=IFERROR(helper formula, "")
Then see if that updates your Move Row formula!
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
The helper column formula doesn't have any errors. Here is that formula:
=JOIN(Month@row:Team@row)
By putting in the IFERROR, it is just going to ignore the errors and doesn't fix the issue.
Thank you for responding.
-
How is your "Month" column being populated?
Formula errors are like dominoes - if there is one cell with an error in a range and then you reference it in a different formula, the original error will be displayed in your second formula. I'm curious to see if the error you have right now is actually an error from a different formula. That's where the IFERROR will help... if there are any errors in your JOIN formula, it will prevent this from affecting your IF(AND formula. Does that make sense?
Can you try updating the JOIN formula just in case?
=IFERROR(JOIN(Month@row:Team@row), "")
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
That worked. I had to do a few things to get it to work.
Changed the formula in the Move Row column to:
=IF(COUNTIFS([Action Plan]@row, ISBLANK(@cell)), IF(COUNTIFS(Helper:Helper, Helper@row) > 1, 1))
I also had to change the ISTEXT to ISBLANK.
Then I added the IFERROR to the formula in the Helper column to:
=IFERROR(JOIN(Month@row:Team@row), "")
Everything works now.
Thank you for the help.
-
Wonderful, I'm glad you were able to get it all working!
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!