Adding List boxes to pivot tables

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi,
I`m relitavly new to Pivot tables and was wondering if it is possible to
add additional dropdown list boxes to the table which will allow me select
different summerised data sources. That is vague I know but I guess I want a
situation where there are selectable criteria (list boxes) which are
external to the piviot table but when actioned will affect the data
contained within. Am I making any sense?
TIA ......John
 
Hi John
it can be done using defined names and the function INDIRECT. A shor
instruction:
1. You have to different data sources on sheet1 and sheet2 both within
the range A1:B99
2. You place your listbox in cell A1 on sheet 3 (you can insert either
'Sheet1' or 'Sheet2')
3. Define a name (goto 'Insert - Name - Define'). In my exmaple i use
the name 'test_pivot'. As reference enter the following
=INDIRECT('Sheet3'!$A$1 & "!$A$1:$B$99")
Note: If your sheet names contains blanks use =INDIRECT("'" &
'Sheet3'!$A$1 & "'!$A$1:$B$99")
4. Create a pivot table. As data source enter the defined name
test_pivot

If you change the table name in cell A1 the pivot table will change
(use a refresh on the pivot table first)

I also recommend lookin at the following website for variable pivot
tables:
http://www.contextures.com/xlPivot01.html

HTH
Frank
 
Think you are referring to the fields dropped in PAGE area

Try this:
Right-click inside your pivot table > Wizard
Drag one or 2 fields and drop inside the PAGE area
Click Finish

In your pivot table:
The fields within the PAGE area will appear on the top-left
and would have the drop lists you are referring to.
 
Back
Top