Pivot Tables

[Guest]

I need to ask few question on pivot table:

First is when i make file workbook protected and hide master sheet on which
table is based, file gets corrupted...
Secondly, can i hide pivot functions and toolbars for users. Like when user
uses Pivot table, he can know what are the fields in source, formatting
changes etc, Can i disables these functions and allow users only drill down
or options select or filtering functions.. Please advice

 

[Roger Govier]

Hi Sandy

Firstly, hiding the sheet upon which the Pivot Table is based before
saving shouldn't corrupt your file. There must be some other reason.
For information on disabling PT features take a look at
http://www.contextures.com/xlPivot12.html

 

[Guest]

Sir Have tried it again. In this there were 2 sheets, One where data was
present and other report. I hided the data sheet and then passowrd protected
workbook:
When i send the file to other user, and user opened it up (we both are using
same excel 2003) it gave following error:

" Errors were detected in "test.xls" but microsoft office excel was able to
open the file by making repairs listed below.Save the file to make these
repairs permanently."
PivotTable Report "Pivot Table" on '[test.xls]Sheet2' was discarded due to
integrity problems."

This way I cant hide source data from users which is very critical for me. I
have seen this on many occassions and hence i only hide source sheet without
password.

 

[Guest]

Also Sir, am referring codes for restricting pivot table but its not
functioning. Please help. Even after providing codes, i can do formatting,
remove add fields, etc. Toolbars are usable.

 

[Roger Govier]

Hi Sandy

Just to test that what I had said was correct, I created a 2 page file.I
set up a small set of data on page 1.
I created a PT, allowing it to create the table on a new page. I deleted
all pages other than the original with data, and the sheet containing
the PT. I hid the data page, then protected the workbook and saved the
file.
I sent the file by email, to another computer within our building.
I opened the file on the second computer, no problems.When trying to
unhide the other sheet, the option was greyed out because of the
Workbook protection.
The Pivot table can be used to select or deselect items from view, but
no drill down is possible as it cannot access the source sheet.
The ability to control this selection ability can be modified following
the link I gave you previously.

I am using XL2003. The recipient machine is running XL2002 (but I don't
think that is an issue).
Try it again with a brand new simple test file as I did and see what
happens. If that works, then the problem must be with your original PT
and /or data file. If it doesn't work then I'm afraid I have no other
ideas as to why it doesn't work for you.

--
Regards

Roger Govier

Sir Have tried it again. In this there were 2 sheets, One where data
was
present and other report. I hided the data sheet and then passowrd
protected
workbook:
When i send the file to other user, and user opened it up (we both are
using
same excel 2003) it gave following error:

" Errors were detected in "test.xls" but microsoft office excel was
able to
open the file by making repairs listed below.Save the file to make
these
repairs permanently."
PivotTable Report "Pivot Table" on '[test.xls]Sheet2' was discarded
due to
integrity problems."

This way I cant hide source data from users which is very critical for
me. I
have seen this on many occassions and hence i only hide source sheet
without
password.

Hi Sandy

Firstly, hiding the sheet upon which the Pivot Table is based before
saving shouldn't corrupt your file. There must be some other reason.
For information on disabling PT features take a look at
http://www.contextures.com/xlPivot12.html