Check Box Data Validation

[Banzai Otis]

Hi all,

First post, but I have been using (and appreciating) this forum anonymously for a while now. I am using Excel 2007 on Win XP, and I'm stumped over validating which check boxes are checked.

I have a number of check boxes, all of which are linked to cells off to the side which will be hidden. There are some combinations of check boxes which shouldn't be allowed, and I am trying to work out a way to enforce this. I am restricted from using VBA or Macros, otherwise this would be a snap!

For example, assume I have check boxes linked to cells A1,A2,B1,B2. The rule is that neither both A's nor both B's can have their respective boxes checked at the same time. I have a formula that works when I put it in to another cell:

=NOT(OR(AND($A$1,$A$2),AND($B$1,$B$2)))

This successfully returns FALSE when either both A's or both B's boxes are checked, and TRUE otherwise. However, I can't get this to work with any kind of data validation. I've tried using this formula directly in the data validation, and also putting it in a cell and using the value of that cell for data validation. In all cases, validation is triggered if I manually change the cell being validated, but not if I change it's value by checking the linked check box.

I am obviously missing something conceptual about what triggers validation, but my googling has failed me. Thanks for taking the time to read this.

Jason

 

[Smitty]

Welcome to the Board (as a poster anyway)!

Why not use Option Buttons instead? They'll only allow one selection per group.

HTH,

 

[Banzai Otis]

Thanks for the fast reply Smitty, but I don't think option buttons will work for two reasons:

1: I need to have the option of not selecting anything from the group
2: To have multiple groups, you need to put them in a group box. Unfortunately, my groups are not adjacent to each other in a way that you could put a box around them.

To clarify, the A and B thing was a simpler example. I actually have a 3x3 grid of 9 check boxes. If they are linked to A1:C3, then the combinations which are not allowed at the same time are (B1,A2), (C1,A3), and (C2,B3).

Any other thoughts?

 

[Banzai Otis]

So, I wouldn't call it solved, but I think I came to an answer here:

As far as I can tell, data validation only applies to values that are manually entered into a cell. It will not be triggered if a cell's value is updated by some other means. So, cells that update based on a linked check box will not trigger data validation. Formulas will trigger data validation if their evaluated value is invalid at the time the formula is entered, but if later changes to other cells result in the formula returning an invalid value it will not trigger data validation. What a pain.

My solution was to use conditional formatting - which is triggered whenever a cell is updated (manually or otherwise). I wrote a function to test for invalid combinations of checked boxes and used it to highlight when there was a problem. Then, I had to apply this test in an if statement for every other formula in my workbook that would break if invalid check box combinations were checked (it would cause circular references in some cases). Even more of a pain, but at least my spreadsheet doesn't break.

Hope that helps anyone struggling with this.