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)

## No comments:

## Post a Comment