If(AND) across a range

Tim Kuykendall

I have a hierarchy of checkboxes.  I am trying create a IF(AND) formula to at the Level 1 checkbox to be checked once all the Level 2 boxes are checked.  I am able to make it work when I reference each of the cells , for example:  =IF(AND([Map Complete]18= 1, [Map Complete]19 = 1, [Map Complete]20 = 1), 1, 0).  When I do it this way and I add a new row, it doesn’t know to that I’ve added. 

 

I tried using reference: =IF(AND([Map Complete]18:[Map Complete]20 = 1), 1, 0) but I get an error of “#INVALID OPERATION”.

 

Also, can you add formulas to Checkbox column types?  When I tried I got an error of “#BOLLEAN EXPECTED”

 

Any suggestions?

L_123

you can use any type of formula in any type of column*. The only thing that is limited is the output. for example a date column must always output a date.

Your main issue in the last formula is the use of the AND. Instead of using and, try a countif. =IF(Countif([Map Complete]18:[Map Complete]20, 1) = 3,1,0) or something like that.

 

*quick edit: Except for contact columns. Those are screwy.