Do not allow Middle or Last name to be entered until First Name is chosen from drop down list.

In this example you will not be allowed to enter the Middle or Last Name before choosing the First Name from the drop down menu.
**Note that Ignore blank is not checked.


Below we can see the error because the First name was not select before the middle name was entered.
This will not allow data to be entered in columns B & C before Column "A"'s Dropdown has been selected.



Watch Video

Validate Todays date. Date can not be less than Todays Date else error

In this example we will not allow the date in Cell E2 to be older than the date in cell E1.

Using OFFSET: Validation1 controls the content of a Second Validation list

1. Create a validation list of the header names. In this example that would be cells A1, B1, C1
2. Create a second validation list. This validation list will be controlled with a formula.
   Note!: The maximum dynamic number of entries in this example will not exceed 200.
   You can change the number 200 to whatever you think is needed.
   
   The formula for the second list will be a "offset" formula.
   =offset(Referance,rows,cols,[height],[width])
   
   Here is the formula to use for the Second  validation list.
   =OFFSET(A1,1,MATCH(E1,A1:C1,0)-1,counta(offset(A1,1,MATCH(E1,A1:C1,0)- 1,200,1)),1)

Next we need to reset the second validation list when the first list changes.
Note!: Offset can be slower than using Index but Index can not use text were offset can.


Watch the Video

Using Index: Validation1 controls the content of a Second Validation list

Using Index may be faster than "offset" but is limited to using Numbers rather than Text and you must use a "Named Range" containing the "Index formula"
in the second Validation List rather than placing the offset formula directly in the second Validation list.

You can see here that the index formula is named "List" We will use this name in the Validation of the second Validation List.


The VBA will reset the second list just like Offset above.