Controlling which columns are visible when drilling down in a pivot table

[EliseFreedman]

I have got a workbook which has been in use for a number of years now
Within the main spreadsheet in the workbook are a few "helper" columns which are hidden from view for end users.
I have got another sheet within the workbook which contains pivot tables based on the data in the main sheet.

End users would like to be able to click on the figures within the pivot tables and view the data behind the figures. My problem is that when they click on the figures, the data appears in another sheet within the workbook and includes all my hidden columns so looks a bit messy.

How can I control which columns a user will see when they click on a figure within the pivot table?

 

[SkipVought]

Elsie,

Interesting problem. Limiting what users can see.

Understand that conceptually, a spreadsheet application like Excel is deliberately designed to bypass the restrictions that programers normally impose on data management programs. Excel is like a fancy DIY data management system with almost no holds barred.

How about the Texas Two-Step?
Step 1. Create a clean table, sans helper columns and
Step 2. Do your PT on the Step 2 table.

Just a thot y'all!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[combo]

If the helper columns are not used directly in pivot table, arrange the underlying table in the way that used columns are on the left, unused on the right (or opposite - if you like) of the table. Next change the pivot table source and exclude helper columns. Double-click displays only data from source range.

As it is a VBA forum, a brute method for disabling double-click in worksheet if macros are enabled (worksheet module):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
End Sub

combo