Advanced Validation Techniques in Excel

Assuming that one knows validation in excel, i am posting this blog which desribes advanced validation techniques in excel.

First let's talk about the problem/scenario which will help one in visualizing what i am talking about.

You have a column in excel where you have defined a drop-down list of items using simple validation. If you require the entries of drop-down list in another column vary in accrodance with the selection made in the first column, these advanced vaqlidation thechniques will help you.

The first column should be defined in the basic way of creating a drop-down box-either by defining a list or by including in the definition field itself. This should be very easy (Assumption!!!)

Now the corresponding items of drop-down boxes must be named with exaclty the same name of the primary list. Let me now take an example.

Option - 1: Numbers which will include 1,2,3,4,5,6,7,8,9,10
Option - 2: Alphabets which will include A,B,C,D,E,F,G,H....
Option - 3: Symbols which will include !,@,#,$,%,^,&,*,(,)

In this case Numbers, Alphabets and Symbols will be your primay validation column. Based on the selection in this column, the next column should include items in drop-down box. Now these items should be included in a different column and their lists should be named as Numbers or Alphabets or Symbols-which ever applies. Then in the second column, include the following formula. This will automatically inter-link the primary and secondary columns.

=indirect(Primary Column Cell No)

Now there is another problem which might arise. IF somebody changes the selection of the first column after making a choice in the second column, then one can use the following formula to highlight the error made. This should be defined in the conditional formatting of the secondary column.

=ISERROR(MATCH(Secondary Column Cell No,INDIRECT(Primary Column Cell No),FALSE))

Now this completes the procedure of creating linked drop-down boxes with loose ends tied.

I assume that i was clear enough in describing the problem and solution. In case if i am wrong, one can leave a message describing their question which will be addressed.