Lock Page Field in a Pivot Table

[Timothy via OfficeKB.com]

I have a page field in a pivot that I need one value selected in. This
selection is critical to the legitimacy of the pivot table data. I need to
lock this selection so that the page field cannot be removed from the table,
nor any other value be selected (There are only two). I know I can just only
include the rows that contain this value in the source data, but the source
data is huge and I need to have some pivots include rows with this value and
some pivots exlude rows with the value. I am using microsoft query to link
to a table in an Access database to feed/update the pivots. The problem is
the size. There are several pivots in this workbook and they are all
created/based from the initial pivot, so the file size doesn't really
increase much with additional pivots. If I create two separate queries with
one to include and one to exclude this field value, then the size of the file
doubles (Big Problem). This workbook is used by many end users with extreme
variances in computer skills. I need to try and make this foolproof. Any
suggestions would be greatly appreciated. I am using Excel 2003.

Tim

 

[Guest]

Perhaps hide the row that contains the pagefield you want locked.

 

[Timothy via OfficeKB.com]

Tom,

That was my first thought, but if you add or remove any page fields, then the
hidden field comes into view and the subsequent field moves to the hidden row.
These reports are manipulated extensively by many different users, many of
which have very basic computer skills. I just need to make sure that this
one field stays in the page field section at all times and cannot be changed.

Perhaps hide the row that contains the pagefield you want locked.

I have a page field in a pivot that I need one value selected in. This
selection is critical to the legitimacy of the pivot table data. I need to

[quoted text clipped - 13 lines]

Tim

 

[Guest]

If you want to give them full control of the pivot table except for one
field, I don't believe there is any support for that. Why couldn't the user
just remove it as a page field? You could always reset it with a macro, but
the user could disable macros. I don't see a quick and easy solution, but
someone else may have some ideas.

--
Regards,
Tom Ogilvy

Tom,

That was my first thought, but if you add or remove any page fields, then the
hidden field comes into view and the subsequent field moves to the hidden row.
These reports are manipulated extensively by many different users, many of
which have very basic computer skills. I just need to make sure that this
one field stays in the page field section at all times and cannot be changed.

Perhaps hide the row that contains the pagefield you want locked.

I have a page field in a pivot that I need one value selected in. This
selection is critical to the legitimacy of the pivot table data. I need to

[quoted text clipped - 13 lines]

Tim