Excel Depended Lists Data Validation

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:

Excel Depended Lists Data Validation.1

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:

Excel Depended Lists Data Validation.2

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:

Excel Depended Lists Data Validation.3

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:

  1. A table of City/State rows
  2. 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:

Excel Depended Lists Data Validation.4

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:

Excel Depended Lists Data Validation.5

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

Excel Depended Lists Data Validation.6

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)

Excel Depended Lists Data Validation.7

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.

Advertisements

Author: dave

Consider myself kid in soul and naive by choice. I am interested in people, technology and business and thrive when they all work together. My favorite quote and motto is that “You can fool some people some times but you cant fool all the people all the time” ― Bob Marley

1 thought on “Excel Depended Lists Data Validation”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s