Adding a Checkbox conditionally

RyanGS

I have a condition where I'd like to have a cell blank, until anything is entered into a cell on the same row.

For example, if Column 1 is blank, there is no checkbox in Column 2.

If any data is entered into Column 1, add a checkbox to Column 2.

The difference being that if Column 1 is blank, and data is entered into Column 3, then no checkbox will appear.

Is this possible?

Nic Larsen

Try this. If Column 1 is blank, it'll not check the box. If not empty, check the box.

=IF(ISBLANK([Column 1]@row, 0, 1)

I am not sure I get what Column 3 has to do but if you expand a bit, I can add that into this formula. 

 

lmarchisio

I'm not sure this is possible as you describe.  Checkboxes are a function of how the column is formatted. 

Could you get around this by leaving it as a text/number column and writing a formula that leaves the cell blank until some other condition is met, then add a number like 0 or 1 as a way to trigger additional formulas.

have to admit to being curious as to what you are trying to do.  I've never come across a situation where making the checkbox itself appear was the goal.  check and uncheck, yes.  but never appear and disappear.  

Andrée Starå

Hi Ryan,

Do you want to hide the checkbox so the cell is blank? (without the square)

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

sean59916

Hi, 

 

Adding to Nik's response - try this in column 2 to find all the conditions that you stated in the question : 

=IF(ISBLANK([Column1]1), IF(ISBLANK([Column3]1), 0, 0), 1)

I hope this helps?

 

Sean

RyanGS

It's a strange condition. I've added a better photo explaination below. 

I don't want to add a checked box, I want to add a checkbox that is able to be checked. This may not be possible.

Paul Newcome

Put this in your checkbox column...

 

=IF(ISBLANK([Primary Column]@row), ".", 0)

 

You can then set a Conditional Formatting rule to change the font and cell fill colors to both be white if Primary Column is ".".

 

Once data is put into the Primary Column, it will change from what appears to be a blank cell to an unchecked box.

 

Keep in mind... Once the box is checked, it will delete the formula out of the cell.

Gali Beh

Thanks, Paul, I had the same need, and your solution worked perfectly!

LEllis

https://community.smartsheet.com/discussion/comment/167656#Comment_167656

This solution worked for my purposes, but I noticed that if I make the formula into a column formula, I am no longer able to check the checkbox. Are checkboxes only usable as cell formulas?

Paul Newcome

@LEllis When you manually enter data (or check a box), it removes the formula from that cell entirely. If you have a column formula applied and then change one cell manually, it is no longer a column formula, so they lock it down from being manually edited. If you want the ability to override, you would need to leave it as a cell formula.

It isn't so much anything to do with checkboxes but how column formulas vs cell formulas in general act.

This is actually a relatively old thread though, so there may be another approach that would work better for you now that there are clear cell and change cell data automations.

What exactly is it you are wanting to do? What column are you using to either hide or unhide the checkbox?

LEllis

https://community.smartsheet.com/discussion/comment/363705#Comment_363705

Oh, of course, that makes total sense!

So I have three checkbox columns to indicate where a group needs to sign off on a task. The number of sign offs is based on the group responsible. Most tasks will have 2 sign offs, but some will have 1. Checking the box indicates sign off is complete.

Here is a redacted version of the Smartsheet using a version of the formula described in this thread to show/hide a checkbox.

My interest in using a column formula is because the number of tasks may increase or decrease. I am pulling the data dynamically from a different sheet using your Filter solution, actually! So basically, if the tasks change or the group responsible were to change, it would be convenient to have the checkboxes take care of themselves. I hope that makes sense.

I have not dived into automations yet, but it sounds like I should.

Paul Newcome

@LEllis Do you have a set logic that can be used to determine which boxes need to be checked on each row? If so, I am thinking we can use a hidden helper column with a formula to have something output to indicate which box(es) should NOT be checked. Then you can use a change cell automation to put a "." in the appropriate column(s) that don't need filled and combine it with the conditional formatting.

LEllis

https://community.smartsheet.com/discussion/comment/363892#Comment_363892

Yes, the logic is:

If [Group Responsible] contains A or B, then the task requires two sign offs--one from A/B and another from C. If [Group Responsible] contains group C, then the task requires only group C sign off.

Said another way:

Group A takes the following sign offs: A and C.

Group B takes the following sign offs: B and C.

Group C takes the following sign off: C.

LEllis

https://community.smartsheet.com/discussion/comment/363892#Comment_363892

I was able to set up some very simple automation workflows that are working great! Thanks for pointing me in that direction.

Paul Newcome

@LEllis Happy to help. 👍️