• 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.

Help with designing Pivot Table

A

arishy

Member
The table consists of Date/amount/type
I have a hidden column in the table that does calculated field ( I do not want the user to see it).

Now I want create a pivot table Row Date Columns Type. Good so far.
I need to filter based on the hidden column !!!!! This is my first question. Can I do it.?

My second question is a bit complicated. Let me try to explain

Once I filter the data based on the hidden column( say value x); I further filter on type.
(say Value AAA)The result is one row(In the PT) which gives me the total amount for specific hidden column value X; AND specific Type(AAA) (that is why it is one row result).

For me to finish off. I need to Zero OUT the amount in ALL THE RECORDS that represent that type for that hidden column filter. ( I believe I need VBA to do that, and I do not know how)

So, to recap

PT will give me the following for a given hidden column value X:

Type AAA amount 500

This is translated to records in the source table to:

type/amount with (hidden column x)
AAA/100
AAA/200
AAA/200

May be I just forget about PT and do the whole thing in VBA. Your input will be greatly appreciated.

For the sharp eyed guys and gals, I will answer this question:
Why you hide a field that you will use to filter the table.??

All I want to say here is that I do not want to confuse the data entry person with data that he cannot understand.
 
Good day arishy

Why try to hide and filter, why not just use a slicer to display what you want the viewer to see
 
This is comforting, you are arguing, hence I am not an idiot.
But I am when it comes to using slicer...Never used it ....Can you give a hand
 
Good day arishy

"This is comforting, you are arguing, hence I am not an idiot." nice but not arguing...discussing :D

Slicer are a very good tool and you can do a lot with your data using them, please look at the links for help, the first one is a short video to show just how easy it is to set them up and the second link gives more detailed but easy to understand information on slicers.



http://blog.contextures.com/archives/2013/05/14/use-slicers-to-filter-a-table-in-excel-2010/
 
Just to answer you question about the possibility to filter with Pivot... yes you can!
Just place them in the "Report Filter"-box and you get the option to select/deselect (above top left corner of you pivot).
 
Good day Xiq

I think it is a bit more than just a straight forward pivot filter, it is more to filter with data hidden
 
You must log in or register to reply here.
Back
Top