Check Box Question

Brendan West

Can you make a requirement to fill out specific boxes before you are able to check the checkbox? So basically you can't check the box unless Column/Row A1 -- B19 -- and M24?

Paul Newcome

Possibly. Are you able to provide a screenshot with some mock data for reference?

Brendan West

So the column with the red and yellow highlighted check boxes are what I want to prevent from being checked until the boxes that are highlighted yellow filled out. Once their are all filled out, then it'll allow you to check the box.

Paul Newcome

You can't "lock" the cells quite like you are looking for, but you can use a formula and conditional formatting to "hide" the box until it is allowed to be checked.

For example, if I wanted to hide the box until the Contractor and Phase columns were populated, I would use a formula such as

=IF(AND(Contractor@row <> "", Phase@row <> ""), "", ".")

This will output a period if either (or both) of those cells are blank. Then I can use conditional formatting to change the text color to match the background color when the row has a period in it.

This will effectively "hide" the checkbox until both the Contractor and Phase cells are populated. Then it outputs a "" (blank) which will reveal the checkbox.

Brendan West

So I place the formula in the check box box? Sorry just trying to place the formula in the right area. Could I place as many boxes I need in there?

Paul Newcome

The formula would go into the checkbox, then you would use Conditional Formatting on the checkbox column.

I'm not sure what you mean by your second question though.

Brendan West

If I want to have 10 different cells to be filled out prior to the check box being unhidden, is that going to work?

Paul Newcome

Yes. You would just need to include those 10 cells in the IF/AND formula.

Brendan West

So for example of two of the cells, would this be correct?

=IF(AND([<- Subject*]246 <> "", [<- Actual Finish*]246 <> ""), "", ".")

Paul Newcome

That is correct. Basically you are saying that IF the first cell AND the second cell are NOT BLANK, then output a blank (which leaves the checkbox visible). Otherwise output a period (which hides the box when conditional formatting is set up.