Dependent Data Validation From Pivot Tables

australia Cascading lists and kangaroos? Today, Ed Ferrero shares his technique for creating dependent data validation drop downs, based on pivot tables. Ed's from Australia, and it looks like we'll learn a bit about his country too, as we go through his sample file.


Dependent Data Validation

We've created dependent data validation drop downs before, based on named ranges, or sorted lists. Ed's technique is perfect if you have a large data source, and it isn't sorted in the order that you need. In this example, there's a list of States and Cities, with the cities in alphabetical order.


Create the Pivot Tables

Ed created two pivot tables, one with State in the row area, and one with State and City in the row area.


The State labels don't repeat in the pivot table, so you can't use the sorted table dependent data validation technique.

Create the Named Ranges

Instead, Ed created a couple of named ranges, and some dynamic ranges.

  • The first range is State, which is the list of state names and Grand Total in the first pivot table.
  • The second range is StateCity, which is the list of state names and Grand Total in the second pivot table.

Tip: If you reduce the worksheet zoom to 39%, you can see the range names.


Create the Dynamic Ranges

The first dynamic range is for the City heading in the second pivot table.

  • CityHeader:  =OFFSET(StateCity,-1,1,1,1)

The next two dynamic ranges, StateNo and StateCityNo, use relative references to read the value of the state from the cell to the left of the active cell. For example, if the selected State is in cell A3 on Sheet1, these formulas are used:

  • StateNo:  =MATCH(Sheet1!A3,State,0)
  • StateCityNo:  =MATCH(Sheet1!A3,StateCity,0)

Queensland is the selected State, so StateNo =3 and StateCityNo =5.

Then, the next State is found in the StateCity range.

  • StateCityNext:   =MATCH(INDEX(State,StateNo+1),StateCity,0)

The next State is South Australia, and it's in row 9, so StateCityNext =9.


Create the Dependent List of Cities

Finally, the dynamic range for the list of cities is created.

  • City:  =OFFSET(CityHeader,StateCityNo,0,StateCityNext-StateCityNo,1)

The City range is offset from the CityHeader cell, 5 rows down, 0 columns right, 4 rows high (9-5), and 1 column wide.


Create the Drop Down List

The final step is to create the data validation drop down lists. In cell A3, a State drop down list is created, based on the State range.


In cell B3, a dependent City drop down list is created, based on the City range.


Download the Sample File

You can download Ed's sample file to see how it works: Dependent Data Validation From Pivot Tables. It's a zipped file, in Excel 2003 format.

About Ed Ferrero

Ed maintains an Excel techniques web site at He is based in Australia, and has been a Microsoft Excel MVP since 2006.


14 comments to Dependent Data Validation From Pivot Tables

  • jimi

    I dont really understand the instruction from
    "Create the Dynamic Ranges"

    Would you please explain more details?
    It already took me 2 hours to work out from the Excel sample but I still have no clue.
    Poor brain.

  • sakthivelan


    Nice Excel sheet...

    The result is fine... but more explanation i.e. step by step explanation could be do much better to the viewers....

    V. Sakthivelan

  • Paula

    "The State labels don't repeat in the pivot table"
    Why not? Under field settings click Repeat Item Labels. Would this simplify the procedure for creating the drop down lists?

  • Matt

    Hi Debra,

    After selecting the State and City what would you need to do in order to display the total for that City?

    Thanks, Matt

  • Matt

    Thank you for the advice Debra, it works great.

  • Matt

    Hi Debra, I created another Cascading List with new name ranges, within the workbook, and a different data source using the Dependent Data Validation From Pivot Table. I am getting an error when I try to add the second list. "The source currently evaluates to an error data validation" What could cause this? Matt

  • Darrin

    I really enjoyed this example. The xls example calls out a note that says, to use this example from another tab, you will have to create named ranges for that tab. I would really like to see the example show how to do this because I tried but failed to get it working.

  • rach

    How would you make the State and City range dynamic instead of static?

  • George

    How can this be used across multiple worksheets?
    I have a WorkSheet called CATALOG where I enter all the information.
    A WS called LISTS where I have the two Pivot Tables.
    And several other WS's (VMSystem, IMSystem, FFSystem) that have a different system configurations that are built by choosing from the catalog.

    Fittings (Elbows, Tees, Valve ect)
    Part (2"x3", Ball, ect).

    This works great but when I use it in another sheet it doesn't work.

    FittingsNo: =MATCH(Lists!A3,Fittings,0)
    FittingsPartsNo: =MATCH(Lists!A3,FittingsParts,0)

    I change it to whatever WS (VMSystem, IMSystem, FFSystem)

    FittingsNo: =MATCH(VMSystem!A3,Fittings,0)
    FittingsPartsNo: =MATCH(VMSystem!A3,FittingsParts,0)

    And it works but only in that WS.

    I can't upgrade to anything above 2003 and I can't use VBA because I cant get VBA to open. (Long story, working on it!)

    Please Help?
    Thank you.

    • George

      Forgot to mention that I have tried to Group the Sheets but no luck but i Am not sure if I did it right.

      FittingsNo: =MATCH(VMSystem:LISTS!A3,Fittings,0)
      FittingsPartsNo: =MATCH(VMSystem:LISTS!A3,FittingsParts,0)

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>