Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

validation of mutually exclusive cells in Excel 2007/2010

Posted on 2012-03-28
5
569 Views
Last Modified: 2012-03-28
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
Comment
Question by:lyteck
  • 3
  • 2
5 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 37778095
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
 

Author Comment

by:lyteck
ID: 37778153
Thanks Brian, That's exactly it! Impressive.

How is it done though, I see no macros?

Lyteck
0
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 37778200
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
 

Author Closing Comment

by:lyteck
ID: 37778365
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
 
LVL 26

Expert Comment

by:redmondb
ID: 37778538
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question