Solved

validation of mutually exclusive cells in Excel 2007/2010

Posted on 2012-03-28
5
531 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

758 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now