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.

DataValPivot01

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.

DataValPivot02

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.

DataValPivot03

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.

DataValPivot04

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.

DataValPivot05

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.

DataValPivot06

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.

DataValPivot07

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

DataValPivot08

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 www.edferrero.com. He is based in Australia, and has been a Microsoft Excel MVP since 2006.

____________

You may also like...

17 Responses

  1. jimi says:

    Hi,
    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.
    thanks.

  2. sakthivelan says:

    Hi,

    Nice Excel sheet…

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

    Thanks
    V. Sakthivelan

  3. Paula says:

    “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?

  4. Matt says:

    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

  5. Matt says:

    Thank you for the advice Debra, it works great.

  6. Matt says:

    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

  7. Darrin says:

    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.

  8. rach says:

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

  9. George says:

    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 says:

      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)

  10. Alex says:

    Hi Guys

    Thanks for the example. I still couldn’t understand the following

    CityHeader: =OFFSET(StateCity,-1,1,1,1)
    Why the offset of -1 instead of +1? and what was this dynamic range doing?

    Many thanks

    • Brett says:

      ‘CityHeader’ is used as a criteria in the ‘City’ formula. {OFFSET(reference, rows, cols, [height], [width])}. It is not dynamic, it is the column label for the list of cities.

  11. Brett says:

    Reviving an ancient thread. I got this to work with the dropdown menus in another workbook!
    1. In the WB with the pivot tables create named ranges for the ‘state’ and ‘statecity’.
    2. In the WB with the dropdown menus follow the instructions in this tutorial, excluding the ‘state’ and ‘statecity’ ranges.
    3. In the WB with the dropdown menus, create the ‘statecity’ and ‘state’ ranges linking them to the first WB. (ex. =’PivottableWB.xlsm’!state).

Leave a Reply

Your email address will not be published. Required fields are marked *