Disable pivot chart ability?

[Ted Horsch]

I'm trying to create multiple pivot tables from a large
worksheet using the show pages feature in pivot tables. I
create the page worksheets and pivot tables with no
problem, but I also need to lock users from getting to the
larger data in the other pages / worksheet. I can simply
lock and protect the top / page rows in the detailed pivot
tables and that almost solves the problem. If a savvy
user goes to the pivot table menu he/she can access the
pivot chart and that way see other data in the worksheet.
How can I disable access to pivot charts for a specific
pivot table? Also, user needs to have drill-down
capability on the pivot table, so I can't disable that
feature. Using Office 2003 on WK2pro.

 

[Jon Peltier]

Ted -

Without writing macros to make it harder for users to do these things,
you have to either enable pivot charts, or disable editing of the pivot
table. But I'm intrigued: you let the user drill down to get some of the
data, but you want to hide all the data. Won't it take just a few double
clicks to reconstruct the original data sheet?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Ted Horsch]

Hi Jon, Thanks for the reply. Here's what I'm trying to
do. I have hundreds of commission transactions that are
confidential. Each one has a sales rep, customer,
supplier and all the related details. No one sales rep
should see any other sales rep's transactions. I create a
pivot table with sales rep as the page, customer as the
row and supplier as the column. The commission dollars
field is in the data area. Then, from that pivot table I
show pages, which creates a new pivot table for each sales
rep. I want them each to be able to drill down into their
individual pivot table. Everything works fine, but in the
individual pivot tables a smart sales rep could access the
other pages. I can solve that problem by hiding and
locking the rows that contain the page dropdown list, but
a really smart sales rep (oxymoron?) could create a pivot
chart from his/her individual pivot table and access
charts of other sales rep's data using the pages on the
pivot chart.

Any suggestions?

Thanks,

Ted

 

[Jon Peltier]

Ted -

Why not filter each sales rep's data into a separate workbook, and build
a pivot table from that. You still retain the master data. Or even
easier, make copies of the master with its pivot table, and filter out
all but one rep's data from each copy, then update the pivot table.

You ought to be able to record a macro to facilitate the process.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Ted Horsch]

Hi Jon,

When I filter the data and then refresh the pivot table, I
get the exact same results as when I do not filter the
data. The pivot table refresh retains the original
rows/columns information rather than using the filtered
data.

I know I could manually copy each rep's data into a
separate worksheet and then build the pivot tables. I was
hoping there'd be a more automated way to do this. The
Show Pages function is very powerful...if only there was a
way to tell it to attach only the data displayed on each
page's pivot table....

I'll probably end up doing something less elegant with
macros...

Thanks,

Ted

 

[Debra Dalgleish]

Jon was probably suggesting that you use an Advanced Filter, which can
extract records to a separate worksheet or workbook. There are some
sample files here that might help you get started:

http://www.contextures.com/excelfiles.html

Under the heading 'Filter', look for 'Update Sheets from Master'