UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Cascaded Validation Lists in Excel    
 
   
KingMartin
post Dec 23 2008, 02:34 AM
Post#1


Retired Moderator
Posts: 10,959
Joined: 9-October 03
From: Prague,CZ / Kiev,UA


Cascaded Validation Lists in Excel
(alias cascaded validation dropdowns)
Tested with
Excel 2003 ENG, WinXP Pro RU
Excel 2007 ENG, WinXP pro ENG
ften there is a wish to have cascaded validation lists, similar to cascaded combos in Access.
I have attached two files to this post, ValidationCascade.xls and ValidationCascadeDynamic.xls.
ValidationCascade.xls
This file demonstrates the usual approach:
The primary list (Pet) is defined as
=Sheet1!$D$1:$F$1
The secondary lists (Dog, Cat, Mouse) are defined as
=Sheet1!$D$2:$D$5
=Sheet1!$E$2:$E$3
=Sheet1!$F$2:$F$3
The name of the pet is choosen from a sublist that is formed on basis of the pet chosen (Dog, Cat, or Mouse)
The validation formula that ensures this (provided the range is selected with B2 being the active cell) is pretty simple:
=INDIRECT(A2)
There is a catch, however. If you use dynamic named formulae, INDIRECT() would fail. It is the second important case where INDIRECT() fails (the first issue is its malfunction regarding links to closed workbooks).
A dynamic range expands with each new entry in the list, thus involving all the entries in the validation ‘combo’.
The second workbook demonstrates a possible workaround (I didn’t find any better so far).
ValidationCascadeDynamic.xls
Again, the primary list is defined as
=Sheet1!$D$1:$F$1
(this name could be also dynamic, but let’s stick to the KISS principle )
The secondary lists (Dog, Cat, Mouse) are defined as dynamic ranges
=OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!$D:$D)-1,1)
=OFFSET(Sheet1!$E$2,0,0,COUNTA(Sheet1!$E:$E)-1,1)
=OFFSET(Sheet1!$F$2,0,0,COUNTA(Sheet1!$F:$F)-1,1)
And the validation formula that does the trick (again, I assume the range is selected with B2 being active) is:
=CHOOSE(MATCH($A2,Pet,0),Dog,Cat,Mouse)
There’s one more catch regarding the data integrity:
Once you have picked up the name, you may change your mind and pick up another pet. This will violate the data integrity, as the name would not correspond with the pet kind anymore.
One approach could be using _Change event to check if the Pet and name are of the same “breed”. Most probably, one would write a code to display a warning and eventually clear the Name cell, awaiting new input.
After some years of writing Excel applications for my team-mates, I wouldn’t recommend to do this. My experience is that the more pop-ups you display or the more cells you automatically change, the more confused the common user is. Imagine poorly written code that shows a pop-up for every single cell when the user clears the entire pet column!
So, my recommendation is to use conditional format that will simply color red both pet and its name, provided they are not of the same “family”. I believe this clearly shows the user that something is not right and that he should change one or the other cell. And one more important thing: if the workbook contains no other code, the user doesn’t have to have the macros enabled, as it is the case with Worksheet_Change() event code.
The conditional format formula is (provided A2:B6 is selected with A2 active before entering the dialog):
=AND(LEN(A2),ISNA(MATCH($B2,CHOOSE(MATCH($A2,Pet,0),Dog,Cat,Mouse),0)))
Where
MATCH (inner) finds the ordinary number of the Pet.
CHOOSE chooses the corresponding names list
MATCH (outer) attempts to find the name in the pet’s names list
ISNA returns TRUE if the name was NOT found, thus firing the conditional formatting coloring the cell’s background red and its font white.
LEN() returns the number of characters (0 for the cell being blank)
AND() ensures that the conditional format applies only to non-blank cells (because we don’t want the entire validated range below our current entries turn red)
Enjoy!

Martin
P.S. Yes, I love cartoons and novels by Steve King wink.gif
Attached File(s)
Attached File  ValidationCascadeDynamic.zip ( 4.4K )Number of downloads: 92
 
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 05:55 PM