Link to home
Start Free TrialLog in
Avatar of Lyteck Lynhiavu
Lyteck LynhiavuFlag for United States of America

asked on

validation of mutually exclusive cells in Excel 2007/2010

Hi:

I have a group of adjacent cells (e.g., A2, B2, C2, D2 on the same row)  in Excel 2007/2010. Only one should be filled. Is there a way to give a visual cue to the user indicating that the input is invalid until at least one cell in the set is filled out. (There can be other sets of related/mutually exlusive fields on the same row.)

A visual cue could mean some kind of conditional formatting where the 4 cells are highlighted in red until at least one cell in the set is filled. Then all 4 cells turn green.
If 2+ cells are filled out, then the 4 cells are highlighted in red again. (Or instead of a color, it could be an error message that appears in A1 for example.)

I'd appreciate a sample spreadsheet to understand how this could be done, if at all possible?

Thanks and best regards, Lyteck
Avatar of redmondb
redmondb
Flag of Afghanistan image

Hi, lyteck.

Please see attached. For fun, the conditional formatting differentiates between no entries (yellow) and more than 1 (red).

Regards,
Brian.
Single-Entry.xlsm
Avatar of Lyteck Lynhiavu

ASKER

Thanks Brian, That's exactly it! Impressive.

How is it done though, I see no macros?

Lyteck
ASKER CERTIFIED SOLUTION
Avatar of redmondb
redmondb
Flag of Afghanistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you for the quick and excellent solution.

1- Do you know if it works on Excel 2007 as well? (I have 2010)

2- Is there a limit on the number of rules that can be stored/active in a spreadsheet? (say, should 100 rules be ok? (there are many such exclusive sets in the actual spreadsheet)

3- Is there a way to label a cell or column or range and refer it in a formula as e.g., "DOB" instead of $A2, etc... so that later, in the conditional formatting formula =COUNTIF(DOB,"<>")>1

This way, the fomula is less dependent on the absolute location of a cell (that might be a separate question, let me know)

Best Regards, Lyteck
Thanks, Lyteck.

1- Do you know if it works on Excel 2007 as well? (I have 2010)
No problem.

2- Is there a limit on the number of rules that can be stored/active in a spreadsheet? (say, should 100 rules be ok? (there are many such exclusive sets in the actual spreadsheet)
Can't say I've ever used so many, but the attached works fine. However, maintaining this number of entries may become messy so you might want to think about a macro to maintain them.

3- Is there a way to label a cell or column or range and refer it in a formula as e.g., "DOB" instead of $A2, etc... so that later, in the conditional formatting formula =COUNTIF(DOB,"<>")>1
Absolutely - check out Names in Excel. The following may be useful...
http://www.homeandlearn.co.uk/excel2007/excel2007s7p6.html
http://www.uwec.edu/help/Excel07/rgnme.htm
http://www.dummies.com/how-to/content/managing-range-names-in-excel-2007.html

(As an aside, you can't use a Name which duplicates a valid cell address. So, DOB is OK, but DOB1 isn't!)

Regards,
Brian.
Single-Entry-100.xlsm