Not Counting Blank cells in Checkbox formula
Hi All,
desperately need your help on this one. I've been searching the forums and tried this formula so many different ways and cannot get it to work.
I have checkbox formula that is simple enough, I want it to check the box if we met the delivery date that was planned. So I'm comparing delivery date planned vs. Delivery date actual. This formula is working fine. IF([Delivery Date Actual]1 <= [Delivery Date Plan]1), 1, 0).
The problem is I do not want it to check the box if either of the cells is empty in either of these columns. I've tried using NOT(ISBLANK and the <>, "" formulas and it either changes all boxes to unchecked or doesn't work at all.
Any help is much appreciated!! Apologies if I'm overlooking something simple!
Comments
-
Hi Renee,
Try something like.
=IF(OR(ISBLANK([Date 1]@row), ISBLANK([Date 2]@row)), 0, IF([Delivery Date Actual]1 <= [Delivery Date Plan]1), 1, 0)
Did it work?
Have a fantastic day!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Or use Andree's suggestion who was 1 minute faster than mine!
-
Thank you guys so much for the quick reply!
Unfortunately they both came back Unparseable
What am I doing wrong?
-
Happy to help!
Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Haha!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Make sure your actual column header names are in there. If there are spaces or they end in a number then you'll need to surround the column names with Brackets. [Column Name]@row.
-
-
Here's a screenshot if it helps.
The Unparseable cells are where I pasted the old formula. The cells that have checkboxes are the old formula but as you can see it's checking boxes for blank cells also.
Thanks again for your help!! -
Can you add a screenshot of the formula you added in one of those unparseable rows?
-
I see... I failed to close the statements correctly...
=IF(OR(ISBLANK([Deliver Date Actual]@row),ISBLANK([Delivery Date Plan]@row)),0, IF([Delivery Date Actual]@row <= [Delivery Date Plan]@row), 1, 0))
That should do the trick. It was missing a closing parenthesis. Did that work? I also adjusted my "" to be a zero which is in essence the same thing.
-
I copied and pasted directly from your post and it's still showing unparseable.
I even refreshed the report just to make sure everything calculated. I don't know why it's not workingI pasted: =IF(OR(ISBLANK([Deliver Date Actual]@row),ISBLANK([Delivery Date Plan]@row)),0, IF([Delivery Date Actual]@row <= [Delivery Date Plan]@row), 1, 0))
-
There is an extra closing parenthesis in the logical statement portion of the second IF.
What is there...
........................IF([Delivery Date Actual]@row <= [Delivery Date Plan]@row), 1, .........
What it should be...
........................IF([Delivery Date Actual]@row <= [Delivery Date Plan]@row, 1, .........
-
Like this
=IF(OR(ISBLANK([Delivery Date Actual]2@row),ISBLANK([Delivery Date Plan]2@row)),0, IF([Delivery Date Actual]2@row <= [Delivery Date Plan]2@row, 1, 0))
Still Unpareasble.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives