Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 751
  • Last Modified:

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
0
lyteck
Asked:
lyteck
  • 3
  • 2
1 Solution
 
redmondbCommented:
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
0
 
lyteckAuthor Commented:
Thanks Brian, That's exactly it! Impressive.

How is it done though, I see no macros?

Lyteck
0
 
redmondbCommented:
Lyteck,

That's correct. I just used Conditional formatting...
(1) No entries (Yellow)...
=COUNTIF($A2:$D2,"<>")=0

(2) One entry (Green)...
=COUNTIF($A2:$D2,"<>")=1

(3) Multiple entries (Red)...
=COUNTIF($A2:$D2,"<>")>1

Let me know if you need more.

Regards,
Brian.
0
 
lyteckAuthor Commented:
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
0
 
redmondbCommented:
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now