Excel changing data validation based on cell value

In Excel, it doesn't seem like you can use a different type of list validation based on changing values in another column. One common workaround is to just define completely different validation rules for different ranges in the same column. This is a mistake, because if you sort or copy the cells, the data validation does not travel with them and you end up with validation rules being applied to the wrong cells.

There is a trick, though. You need to use a formula to define the range that will give you the list values that the validation applies to. For example, if column B defines which validation rule to apply, and you have the list values for each validation rule in the lookup tab, with each column B value in the range A1:D1, and the possible cell values in a list below each B value, you could use a formula like this as the list validation formula, which you are applying to the entire column A.

=CHOOSE(MATCH(B1,Lookup!$A$1:$D$1,0),
    Lookup!$A$2:$A$4,
    Lookup!$B$2:$B$4,
    Lookup!$C$2:$C$4,
    Lookup!$D$2:$D$4)

Experienced Excel users might be questioning my use of the CHOOSE function here, and wondering why I didn't use OFFSET. Usage of OFFSET (and related functions like INDIRECT) should always be avoided if possible. Since Excel's calculation engine cannot determine the cells that an OFFSET formula depends on, every formula that uses OFFSET will always be recalculated when anything in the workbook changes. This can have a very negative impact on spreadsheet performance. However, if you have a large number of different validation rules, the OFFSET version might be preferable, and would look like this.

=OFFSET(Lookup!$A$2:$A$4,0,MATCH(B1,Lookup!$A$1:$D$1,0)-1)