When you have columns in a list that contain (for instance) state names and their corresponding cities, you might find it useful to create lists from the internal range of the subordinate column. For example, in the list shown here you might want to return the range from the City column corresponding to a specified state in the State column shown below.
The key to generating lists from the City column is the following formula.
where State is the column in the list on the LookupSheet tab containing state names and
where TheStateName is the cell containing a data validation list of unique states (Control!C3).
where TheCityName cell (Control!D4) contains a data validation list of cities corresponding to the return value of TheCity.
Basically, what this formula does is return a range from the City column, given a specified state.
The following figure shows the cells with the data validation drop down lists.
The information from these cells can be used for further lookups, which is the subject of an upcoming post. You can download the example file here.