... can be used in Validation.

Assume you have an input sheet where the user selects a Staff Type, then a Pension Plan. The difficulty is that the Plans available depend on the Staff Type.

Set up a table like this with the staff types across the top and the available plans listed under each one.

Boss Manager Junior
Plan A Plan A Plan C
Plan B Plan C Plan D
Plan E

The boss has a choice of three, the Manager and Junior have only two. Name the body of the table(ie all except the top row) "planList" and the top row as "typeList".

To validate the Staff Type cell, lets make it A1 for example, select a List type validation and enter "=typeList" as the source. So far pretty standard, note lists can be horizontal or vertical.

To validate the Pension Plan cell, lets make it A2, choose a List type validation and enter the following formula.

=OFFSET(planList,0,MATCH(A1,typeList,0)-1,ROWS(planList),1)

This says, find the column my selection in cell A1 is within the typeList using the Match function, and subtract one (because Matchs start at 1 and Offsets start at 0) to give the column of the planList table to use.

The Offset function says, give me the part if the planList that is in the appropriate column, and make it one cell wide and as tall as the lookup table might be(ROWS()).

The formula may seem a bit complex if you haven't worked with match and offset before, but a bit of experimentation will get you there.

You can extend this to count the number of rows in the relevant column, so you only see the real values. Hint, use count and counta instead of the Rows() construct.

This is really just scrathing the surface of what is possible in a validation formulae. If you are familiar with "array-formulae" they will also work in the validation list anabling you to sort a list for display and all sorts of other options.

These methods give you enormous power in providing just the right options to your users and allowing you to easily change options without needing to recode all your validation lists by hand. You can use information entered by the user to validate other inputs. You might have a list of people and an input cell for each where you can select their spouse. Use the whole list as the source for the Spouse cell. As the users adds people, the validation lists grows accordingly.

The last bit of the puzzle is that you can even create your lookup formulae as "Named Ranges" and then just refer to the name in the Validation List area. So, only one copy to change to update all similar input cells making the process much faster and easier to manage.