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.


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.

Plan Your Party Seating with Excel

If you're having a party this weekend, you can plan your party seating with Excel. Get this sample Excel seating workbook, enter the guest names on the Lists sheet, then fill the tables by selecting names from data validation drop down lists. After you've assigned a guest to a table, that guest's name disappears from the drop down lists, so you can't accidentally assign a guest to two different seats.

NOTE: There is a newer seating plan here: Excel Seating Plan with Charts

Continue reading "Plan Your Party Seating with Excel"

3 Ways to Prevent Data Entry Errors in Excel

Garbage in, garbage out. You depend on Excel spreadsheets to show accurate data and analysis. Enter the wrong data and the results will be incorrect, and that could have a negative impact on your business or reputation.
Here are 3 ways to help safeguard your data entry. I'm sure you use a few other methods in your worksheets.
Data Validation Drop Down

Use Keyboard Shortcuts

Keyboard shortcuts save you time and can prevent errors. Quickly enter the current date and time, or copy the cell above, instead of re-typing.

  • To enter the current date:  Ctrl + ;
  • To enter the current time:   Ctrl + :
  • To copy from the cell above:  Ctrl + D

Use Drop Down Lists

Instead of allowing freeform data entry, you can add drop down lists to the worksheet. For example, create a list of product names, and select from that list. This prevents typos, and only valid entries are allowed. Then, if a product is discontinued, remove it from the list, and it won't appear in the drop down for new entries.
To create a drop down list:

  1. On a blank worksheet, create a list of items for the drop down list.
  2. To name the list, select all the items, then click in the Name Box, at the left of the formula bar. Type a one word name for the list, such as Product_List then press the Enter key.
  3. On the data entry sheet, select the cells where you want the drop down list.
  4. On the Excel Ribbon, click the Data tab, then click Data Validation.
  5. In the Data Validation dialog box, for Allow, select List.
  6. In the Source box, type an equal sign, then the name of your list:  =Product_List
  7. Click OK.

Highlight Missing Data With Color

Use a bright color to highlight data entry cells that are empty. This makes it easy to spot the cells that have to be filled in
To highlight an empty cell:

  1. Select the cells that you want to highlight (to select non-adjacent cells, press the Ctrl key, then click on cells)
  2. On the Excel Ribbon, click the Home tab, click Conditional Formatting, and click New Rule
  3. In the Select a Rule Type section, click Use a Formula to Determine Which Cells to Format
  4. In the Format Values Where This Formula is True box, type a formula that checks the active cell (Name Box shows active cell address), using double quote marks ("") to test for an empty string. For example: =A3=""
  5. Click Format, on the Fill tab select a color, then click OK, twice.


Select Multiple Items from Excel Data Validation List

You've probably used an Excel data validation drop down list, where you can click the arrow, and select an item to fill the cell. With a bit of programming, you can customize a drop down list, to select multiple items.

Continue reading "Select Multiple Items from Excel Data Validation List"

Prevent Cheating With an Excel Drop Down List

You know how to create a drop down list in Excel, by using the Data Validation feature. Sometimes you get extra fancy, and make a drop down list that depends on the selection made in another list, using dependent data validation.
Dependent data validation works well, but there's a loophole. In the screen shot below, I can change the category to Fruit, even though a vegetable, Cabbage, is selected as the Item in that row. With this loophole, you could end up with some strange data on your worksheet!

Lock the First List

Excel users are very resourceful, and can find ways around almost every safeguard that you set up. However, you can slow them down a bit, by making the first list dependent on the second. They'll have to clear the Item selection before they can change the Category.
Currently, the Category cells have a data validation list that's based on the named range, Produce.
You can change the formula in the Source box, so it only uses the Produce range if the Item cell is empty. In the screen shot below, cell B2 is active, and the Data Validation Source formula is:
If cell C2 is empty, the Produce list will show in the drop down list. If an Item was selected in cell C2, then the drop down list will show contents of cell B2.

Highlight Any Inconsistencies

It's still not foolproof. Users could clear the Category cell, then enter any value. Or they could cut and paste from another cell. As an extra precaution, you could add conditional formatting in column C, to turn the cells yellow if the selected Item is not in the selected Category.
With cell C2 active, the conditional formatting formula is:
The bright yellow colour will alert users if there's an inconsistency in the selections, and make it easier for you to spot any problems.
For example, in the following screen shot, Cabbage and Rutabaga are highlighted in yellow, because the Category has been changed to Fruit. Those items don't appear in the range named Fruit, so the COUNTIF formula returns a zero.

Dynamic Dependent Data Validation Lists

Roger Govier has created a sample workbook and instructions that show you how to create dynamic dependent data validation lists. His technique lets you create multiple levels of dependent data validation, without defining a named range for each list. Instead, Roger's formula uses the INDEX function, with MATCH, to find a column heading, then shows the list from that column.
For example, on one sheet you can list regions, countries, areas and cities.
Then, on another sheet, select a region in column A, and see only the related countries in column B's data validation drop down. In column C, you'd see only the areas in the selected column, then only the applicable cities in column D.

How It Works

With Roger's technique, you'll create four defined ranges, then use two of those ranges as the source for data validation drop down lists. The ranges are dynamic, so you can add more items to any list, or add new lists, and the defined ranges will automatically adjust.
There are full instructions for Data Validation – Dynamic Dependent Lists, and a sample workbook that you can download. Also, Roger Govier's contact information is at the bottom of that page.

Excel Drop Down List From Different Workbook

In Excel, you can create a drop down Data Validation list, so it's easy to enter valid items in a cell. Usually, the list of valid items is stored in the same workbook, but it's possible to set up a drop down from a list in another workbook.

Continue reading "Excel Drop Down List From Different Workbook"