?
Solved

validation of mutually exclusive cells in Excel 2007/2010

Posted on 2012-03-28
5
Medium Priority
?
653 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
[X]
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
  • 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 2000 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

777 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