• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot Table Values Filter

F

Fran Reed

New Member
Hi there.... I have a dataset of Trial Balances by Month. Each Month is a column. Accounts are rows.
I would like to use this set with a pivot table but have the user to be able to 'choose' which Month Value column they want to display.....In other words, I want to have the "values" be only the months I am interested in.
I know I could create the pivot table using VBA but was looking for some other technique.
Any tricks out there to do this?
Fran
 
The problem is that your data isn't really in the optimum setup for a PivotTable. It would be nicer if there was a single "date" column, and within that column the Month was specified.
So, what are our options?
a) Re-arrange our data. Could be painful, but there are several macro-ish ways to help. No good if your data is constantly coming from somewhere else.
b) Train user to edit the PivotTable, swapping out the fields they want as needed. Not impressive, but least amount of work.
c) Write up some VB to swap the field based on user choice.
 
Luke, I totally agree. a. trying to avoid... b. trying to avoid, want more user friendlier. c. sort of stuck doing this with vba... Ok... thanks
 
writting from smartphone.. May be not able to show you properly..

still..
1. Assuming account are In column A. And months are in B:Z.
2. Select the table and press Ctrl D M. (pivot table wizard).
3. Select Multiple Column. (2nd option button)
4. Click next and select table..
5. Click finish.

Now you pivot table is half created..
in the bottom right corner of created pivot table.. You will found the Grand Total cell..

Just double click that one..
It will re-create your table.. The way you want to ..
Now assume this one as your main table... And start working normally to craete new pivot table..
which is capable to select each month.. Or group of month.. Or anything suitable for diging data..
 
You must log in or register to reply here.
Back
Top