CODEWITHSUNDEEP
excelvba
nomad_king
nomad_king

Reputation: 23

Conditional data validation for MS Excel

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.

Upvotes: 2

Views: 2884

Answers (2)

Ioannis
Ioannis

Reputation: 5388

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

enter image description here

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

Upvotes: 0

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11151

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.

Upvotes: 2

Related Questions