PDA

View Full Version : Display Pivot Table values in a userform



tommy1234
02-20-2012, 01:30 AM
hello
i created few pivot tables on a specific worksheet and i have another worksheet that contain pivot table filter in order to filter those pivot tables.
i want to display one of the pivot table values in a user form that will popup after a selection is made and display the values of the pivot table.

thanks

Bob Phillips
02-20-2012, 03:15 AM
You need to be much more specific as to what value you wish to display, is it a filter value, a value from the values area, a column, row, what if there are many etc.

tommy1234
02-20-2012, 07:38 AM
hi
i want to display 2 columns.
the first one is the column that is the filter and the second one is the values refer to the first column.
for example,
if the user choose a manager name in the first column then the second column will display all the projects that are in his responsibility.

Bob Phillips
02-20-2012, 09:00 AM
Is the original data available, because it might be easier to get it from there?

tommy1234
02-21-2012, 12:12 AM
hi
the original data is in another sheet which called "data"
i use the pivot table for group values.
i have many projects for each project manager and each manager has many project manager below him.

Bob Phillips
02-21-2012, 02:29 AM
On the output sheet
A1: =data!A1
A2: =IF(ISERROR(SMALL(IF(data!$B$1:$B$20="Bob",ROW(data!$B$1:$B$20)),ROW(A1))),"",
INDEX(data!$A$1:$A$20,SMALL(IF(data!$B$1:$B$20="Bob",ROW(data!$B$1:$B$20)),ROW(A1))))

A2 is an array formula, and you copy it down as far as you need.

You can have the manager name in a cell and refer to that instead.

tommy1234
02-22-2012, 01:36 AM
thanks, but...
how do i put it in a userform, so when the user will choose a name he'll get a popup form where the list of the project he's reponsible will apear.

Bob Phillips
02-22-2012, 02:27 AM
I would suggest that you have a hidden sheet where you create the list that I showed earlier, then have a listbox on the userform that you load from that hidden sheet.