Conditional data validation for MS Excel

Question

i read some tutorials that help out with conditional (or dynamic) data validation, but its usually dynamically generating list item choices based on previous selection etc.

what i want is, lets say i have

columnA : columnB
telephone_number : 911
name: peterparker

is it possible for me to dynamically specify data validation rules on columnB based on what is selected in columnA? if its telephone_number, i want to check that the corresponding cell in columnB is a natural number, and if it is a name,i want to check for a string for a certain length. of course i limit the string options that the user can input in columnA.

Yes, it is possible. You can build an array in VBA, and use the array as the source of the validation list. — David Zemens
– David Zemens, Commented Oct 8, 2013 at 13:50

LS_ᴅᴇᴠ · Accepted Answer · 2013-10-08 13:57:21Z

2

Following formula on custom validation rules will do:

=IF(A1="telephone_number";ISNUMBER(B1);IF(A1="name";LEN(B1)=8;TRUE))

This is example for cell in B1. You may choose a wider range and combine $ to adapt you formula to all range.

answered Oct 8, 2013 at 13:57

LS_ᴅᴇᴠ

11.2k1 gold badge27 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ioannis · Accepted Answer · 2013-10-08 15:51:56Z

An alternative to data validation is Conditional Formatting. The benefit of the latter is that it is more versatile. We can have the following setting:

enter image description here

Then if we define a name, DynamicValidation as follows:

enter image description here

the formula at the validation box is

=EVALUATE(SUBSTITUTE(VLOOKUP(Sheet1!$A2,Sheet1!$D$2:$E$3,2,0),"()","("&ADDRESS(ROW(),2)&")"))

EVALUATE cannot be used in any sheet cell, but it can be used in Named ranges. However, you will have to save your book as macro-enabled, because it is a legacy macro, originating from Excel 4.0. What it does is the following:

Looks at the value in List1 and finds the formula that needs to be checked (VLOOKUP).
Subtitutes "()" with the cell value of interest (and adds back the parentheses).

Then if you open a conditional formatting box (Home tab, Conditional Formatting, New Rule, Use a formula to determine....) and input the formula =Not(DynamicValidation) like this:

enter image description here

then each cell that does not adhere to the formula will turn red. I tried that with data validation as well, but it does not work unless we build the depencency tree every time we change List 2.. I am not sure why.. But only conditional formatting works :) Here are some screenshots:

enter image description here

The benefit is that we can change the validation criteria without hard-coding. I hope this helps!

Collectives™ on Stack Overflow

Conditional data validation for MS Excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related