controlling a pivot table using a combo box
 

I've been looking for some help on this and I haven't had any success.
I found a macro that controls multiple pivots, but not one that
controls a pivot table from a combo box. Does anyone know how to do
this?

Your help is appreciated.

controlling a pivot table using a combo box
 

I just learned how to do this at the Excel User Conference in Fort Worth.

You need to add the combo box from the forms toolbar.

The items that will fill the combo need to be entered into a list somewhere
in Excel, and be sure to include the item 'All' at the top
This range becomes the input range for the combo box.

You need to right click on the combo and pick Format Object.

The properties you need to change are the Input Range and the Cell Link.
Input Range should be as described above. Cell link can be anywhere out of
the way.

Then you need a formula next to the cell link cell.
=INDEX([X],[Y]) where [X] is the input range and [Y] is the cell
link cell address.

The next part is also simple:

Record a macro of the type change you would want to make to the pivot
table, and see what it looks like in the module that is created. Just change
this so that it looks like :

Activesheet.PivotTables("blahblahblah"").PivotFiel ds("BlahBlah").CurrentPage
= _
ActiveSheet.Range([Y]).Value 'where Y is the address of the
index formula, in quotes of course.

Tie the combo box to this macro by right clicking on it and choosing
"Assign Macro"

Hope this works for you.


wrote in message
oups.com...
I've been looking for some help on this and I haven't had any success.
I found a macro that controls multiple pivots, but not one that
controls a pivot table from a combo box. Does anyone know how to do
this?

Your help is appreciated.

controlling a pivot table using a combo box
 

Thanks William. It worked perfect.

controlling a pivot table using a combo box
 

I learned it from Mike Alexander. If you like working with Pivot Tables:

http://www.mrexcel.com/pivottablebook.shtml


wrote in message
oups.com...
Thanks William. It worked perfect.

controlling a pivot table using a combo box
 

What would the changes in this method for OLAP based Pivot Tables?

Thanks,
Pasha

"William Benson" wrote:

I learned it from Mike Alexander. If you like working with Pivot Tables:

http://www.mrexcel.com/pivottablebook.shtml


wrote in message
oups.com...
Thanks William. It worked perfect.