Dependent Data Validation From Pivot Tables

dependent data validation from pivot tables

australia Cascading lists and kangaroos? Today, Ed Ferrero shares his technique for creating dependent data validation from 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.
____________

21 thoughts on “Dependent Data Validation From Pivot Tables”

  1. 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. 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. “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. 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. 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

  6. 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.

  7. 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.

    1. 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)

  8. 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

    1. ‘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.

  9. 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).

  10. This works very well, except when I apply a filter. I get a random error when I filter on certain choices in the filter box. Some choices work fine, others are returning an error in the Next formula, even though the StateNo and StateCityNo return accurate row counts. What is happening and is there a way to fix it?

  11. Thank you so much for this.
    its an old example but it worked really well.
    however Im running into a situation where my “State” is somehow limiting the amount of data i can pull.

    these are my data validation formulas im using in my program
    so in my pivot table my list of “states” goes to around 13000
    however my list of “CITIES” goes far beyond that about row 20000

    coincidentally the i cant pull any “cities” that go past 13000
    so to test i added another “STATE”
    and now i can pull up to 13001

    why is my formula restricting the amount of data i can pull.

    does the offset mess with this?
    i used it to keep my program running no matter how many “States” are added.

    Formula
    =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A$1:$A$1000000),1)
    =OFFSET(Sheet2!$D$1,0,0,COUNTA(Sheet2!$D$1:$D$1000000),1)
    =OFFSET(StyleSize,0,1,1,1)
    =MATCH(Sheet2!$P$14,Style,0)
    =MATCH(Sheet2!$P$14,StyleSize,0)
    =MATCH(INDEX(Style,StyleNo+1),StyleSize,0)
    =OFFSET(SizeHeader,StyleSizeNo-1,0,StyleSizeNext-StyleSizeNo,1)

    I hope someone can help me with this as this thread is very old. lol

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.