Now that we experimented with simple Data Validation, let’s go back to try out more dynamic Data Validation.

Our ultimate goal is to create a State/City dependent columns. In other words, we want to create dependency between two cells. When value is entered in the State cell, the List Data Validation will be dynamically changed to show only the Cities within that particular State.

We will do that in steps: In the previous entry we talked about creating a List Data Validation that is based on a range of cells. We used the following:

*=Top10CaliforniaCities!$A$1:$A$11*

Here is a picture to refresh our memory:

We can achieve the same functionality with just a little bit more dynamic option using the Offset function.

The Offset function builds dynamic ranges out of an anchor cell, an offset from that anchor and a box of width and height.

You can read more on the Offset function here:

http://office.microsoft.com/en-us/excel-help/offset-HP005209208.aspx

To achieve the same range as before but with the Offset function we should write something like this:

*=OFFSET(Top10CaliforniaCities!$A$1,0,0,10,1)*

Which means build a range of a 10 by 1 box from the Top10CaliforniaCities Tab starting from location A1 with no additional offset.

Next, let’s build two new table that will become our helper tables. The first table is a table of City/State records, like the following:

The table above is a list of Cities and their States. The list is ordered first by the State name and second by the City name. This detail is important for the next items to work.

The second table is a list States and some pointers into the row numbers where the City list for that State starts and ends. Something like this:

You can either type the values for start and end manually or calculate it automatically using the Match function. I used the following:

For Column B, cell B2 has the following formula:

*=MATCH(A2,topUsCities!B:B,0)-1*

Which means, search for the first value equal to the value in cell A2 (I.e. Alabama) in the topUsCities column B and subtract 1

Similarly for Column C, cell C2 the formula is:

*=MATCH(A2,topUsCities!B:B,1)*

Which means, search for the first value greater than the value in cell A2 in the topUsCities column B.

More information on the Match function can be found here:

http://office.microsoft.com/en-us/excel-help/match-function-HP010062414.aspx

Finally the formula for column D, cell D2 is a subtraction of the value from column B from the value from column C:

*=C2-B2*

At this stage we have the following assets:

- A table of City/State rows
- A table of all States and a row number references into the first table

With that information at our hands we can now construct a dynamic List Data Validation. Assuming our table looks like this:

With the State column containing the value “Alabama” and using regular List Data Validation of all the US States. We would like column B to dynamically show only Cities in Alabama and look like this:

Or when the value in cell A2 contains Massachusetts the list of options will look like the following:

We do that with the same List Data Validation, with a dynamic list that is generated using Offset and VLookup that dynamically set the list to include only the Cities that are in current State as defined by the value in cell A2:

*=OFFSET(topUsCities!B1,VLOOKUP(A2,States!$A$2:$D$51,2),0, VLOOKUP(A2,States!$A$2:$D$51,4),1)*

VLookup is another very useful function. VLookup searches for the Lookup value in a Table, and returns value in a specific column in that row. More information on VLookup can be found here:

http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx

And that is how this magic is done. There are other methods to achieve similar results with the function Indirect and they bring their own hidden complexity. I may write on that option next.

Reblogged this on Sutoprise Avenue, A SutoCom Source.