Formula help - if cell is blank, checkmark cannot be checked

Loann McGee

Brain cells are missing. I've looked through the formula examples and can't seem to put together the formula I need.

 

We have a column that needs to have text in it before the Checkmark column should be marked with a check. 

 

Here's the scenario: IF column B is empty, checkbox cannot be checked.

 

This will help our team remember that they have to input data into Column B before they mark the row complete.

 

Blessings,

Loann

John Sauber

Hi Loann,

I believe you need a third column to flag what you want. I am assuming the two columns which get edited are called ColumnB (Text/Number type) and ColumnCheck (Checkbox type). Call the third column WrongOrder (Flag type).

 

WrongOrder1=IF(AND(ISBLANK(ColumnB1), ColumnCheck1 = 1), 1, 0)

 

In order to keep the sheet as clean as it was before the column was added, you could hide the WrongOrder column, and run conditional formatting based on the flag presence to highlight the cell that was checked and shouldn't yet be.

JohnHinkle

You could also set up a formatting rule that will color the background of the checkbox cell if the corresponding columnB cell is blank. It wouldnt prevent the users from checking the box but it could be a warning not to. 

John Sauber

I like your suggestion, John. One color could be the warning, and another conditional formatting rule could be where columnB is blank AND ColumnCheck is checked could be a stronger color.

Loann McGee

John S - I used your formula to no avail. Help?

 

Here's what I did, naming the column as you suggested, but using our existing column names: WrongOrder1=IF(AND(ISBLANK(CIOProcessNotes1), CIOComplete1 = 1), 1, 0)

The result I need is if the CIO Process Notes is blank, CIO Complete does not have a checkbox. 

 

I entered the above formula in the Wrong Order column, saved, then added text to the CIO Process Notes column and saved my changes. The CIO Complete checkbox did not turn to a check. 

 

Blessings,

Loann

Loann McGee

And to clarify, it's not that the checkbox is checked if there is content in the CIO Process Notes, it's that it CANNOT be checked unless there is content. Not sure if that's possible. 

 

Thus, if I try to check the box it won't let me until I have entered the CIO Process Notes.

Blessings,

Loann

John Sauber

Did you use the first part of the formula? That was my way of saying the formula should go into column WrongOrder1. The exact formula for row 1 of WrongOrder would read:

 

=IF(AND(ISBLANK(CIOProcessNotes1), CIOComplete1 = 1), 1, 0)

 

A little bit of careful study of the construction of an IF() statement might have told you this. Just so you know, there is no way to prevent anyone from checking the box. THe workaround that we are suggesting would simply "call out" someone who is circumventing the process you have in place. Preventing someone from checking a box would entail changing the read/write properties of a column, which there is no control over.

Loann McGee

This is the formula copied from my sheet: 
=IF(AND(ISBLANK(CIOProcessNotes1), CIOComplete1 = 1), 1, 0)
It tells me it's unparseable. It is entered into a 3rd column, as recommended.

I'm not noticing any difference in the formulas...what am I missing?

The CIO Process Notes is a Text/Number column and the CIO Complete is a checkmark column.

I even tried a different column - CIO Complete Date - in place of CIO Process Notes, to no avail.

Blessings,

Loann

 

Travis

Loann, this is what our Help Center article on formula says about the #UNPARSEABLE error:

 

Cause: The formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons such as misspelling a column name or using single quotes instead of double quotes.
Resolution: Check the formula carefully. Pay close attention to column names and quoted text.

 

Are the names of your columns "CIO Process Notes" and "CIO Complete"? If so, in your formula they should be [CIO Process Notes]1 and [CIO Process Notes]1 and not CIOProcessNotes1 and CIOComplete1. 

Stephen Uekert

Just another thought.

 

How about taking the ability to check the box out of their hands and force them to fill in contents to make the box check?

 

I just tested a simple IF statement "=IF([Column3]1 > 0, 1, 0)".  This statement is put into the checkbox cell. It's pointing to whatever column you want there to have to be text in. It's saying that if that column greater than zero, then place a check and if not, then don't. The great thing about this is that the greater than zero works for either text or numbers so as long as they put something in the cell, it will check the box.

 

Then you could lock the checkbox column so they can't mark it complete at all unless they fill in the box.

 

Just a thought, take care.

Tim Meeks

Loann,

 

Not sure if you ever got this solved. 

I did something similar to what John H suggested re: conditional formatting.  

 

Set up a rule that states If Checkbox = Checked and Text box is Blank, then change the row or column formatting to Bright Red or someother bright color.

 

I just used this for 3 different cells that had to be synched up when the Task was Complete. It required 6 formulas to link the 3 fields together but works well! 

 

got this idea from another post.. https://community.smartsheet.com/discussion/one-click-task-completion  

 

Loann McGee

Thanks, Tim. I do have a simple formula that is working, but I come back to re-read other options as I'm often pondering more ways to auto populate particular cells. 

Blessings,

Loann

Michael Smith

Stephen, I just tried to use this IF statement:  "=IF([Column3]1 > 0, 1, 0)". Are your sure of the format?  I'm using: 

 

=IF([Completion Date]1116, 1 > 0, 1, 0)

 

Is there some issue with the format. Thx in advance!

 

Michael