Create a customizable pivot table with dropdown to select fields to add to values area

[nsmith]

Hello, I am using excel 2010, and I am working with a school data table that includes fields such as school name, school year, and various demographic data fields such as graduation rate, % attendance, etc. Each row contains the demographic data for a particular school, for a particular school year.

I created a pivot table using data from this table. In the pivot table fields/areas section for the table, the school year field is in the "Column Labels" area, and the school name field is in the "Row Labels" area. I can add one or more demographic fields to the "Values" area to create a table of data for each school.

If possible, I would like to display just one type of demographic data at a time in the pivot table, such as just the graduation rate, or just the % attendance. The ideal would be to have a table with just one type of demographic data values listed, and be able to use a dropdown menu (or something similar) to select what type of data is displayed in the table. The row and column fields would remain the same, but the values would change.

Is there a way to add a filter or dropdown menu to the pivot table, to select which data fields appear in the values area? Or, if there are multiple fields in the data area, is there a way to filter the values fields that are displayed in the table?

Thank you very much!
-nsmith

 

[nsmith]

I looked around online, and was able to find vba code that will add fields to the values area:
PivotTable.AddFields Method (Excel)

You may also need to use code to remove any existing fields from the values area, before adding a new field. DonkeyOte posted the following code on the excelforum.com website.

Public Sub Example()
Dim PT As PivotTable, ptField As PivotField, ptItem As PivotItem
Set PT = ActiveSheet.PivotTables("PivotTable2")
For Each ptField In PT.DataFields
    On Error GoTo CalcField
    ptField.Orientation = xlHidden
    On Error GoTo 0
Next ptField
Set PT = Nothing
Exit Sub

CalcField:
Set ptItem = ptField.DataRange(1).PivotItem
ptItem.Visible = False
Set ptItem = Nothing
Resume Next

End Sub

Thanks!