Solved

pivot on visible

Posted on 2013-11-06
7
210 Views
Last Modified: 2013-11-08
Hello All,

There is a big main source data sheet. There are filters to filter the data. The pivot table is based on this huge table. What i was thinking – is it possible for the pivot to work on rows which are visible in this worksheet? In other words – pivot takes into account only visible rows….not the hidden rows to show the pivot results….

Rayne
0
Comment
Question by:Rayne
  • 4
  • 3
7 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39629478
Basically to answer it the way you asked it is:
NO pivot will take the full data

HOWEVER

there may be a way around that and it is thru VBA or to change the way the pivot is built say you have a date column and you choose to filter date >= 1/1 and date <= 5/1 then in your pivot you would use this criteria to display not simply field Date. I know that when you have large data and preset tables it is cumbersome to go change on each filtered data the criteria in the table reason why it is best for you a VBA solution.

I could help you in the VBA solution if you want to go that route but you will need to provide a specific example where I would suggest you post your current data (making sure to remove sensitive info) and we can take it from there.

gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39630890
Hi Rayne
Is this what your looking for ??

In sheet1 you have the data that you can filter and see the results in the corresponding pivot table. play with the filters and see the results.

Let me know if this is what your looking for.
gowflow
FilterPivot.xls
0
 

Author Comment

by:Rayne
ID: 39631061
Hello Gowflow,

Thank you for your reply. I have created a new column in the data – this column dictects whtehr a row will be seen by the pivot or not. So if a row has False for this column, the pivot doesn’t see this in the reults. Can you help me a filter or something within the pivot so that it always looks at this column to decide what results to show?
The other thing – this workbook will protected, so users can’t see or change this column filter at all….
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:Rayne
ID: 39631063
Products      Dates      Sales      PivotThis?
1      1-Jan      200      TRUE
2      2-Jan      500      TRUE
3      3-Jan      340      TRUE
4      4-Jan      120      FALSE
5      5-Jan      12350      FALSE
6      6-Jan      1400      FALSE
7      7-Jan      378      FALSE
8      8-Jan      1260      TRUE
9      9-Jan      2000      TRUE
5      10-Jan      14500      TRUE
5      11-Jan      2798      FALSE
5      12-Jan      14502      FALSE
13      13-Jan      240      FALSE
14      14-Jan      2400      FALSE
21      15-Jan      14505      TRUE
21      16-Jan      14506      TRUE
21      17-Jan      3400      TRUE
18      18-Jan      14508      TRUE
19      19-Jan      22      TRUE
20      20-Jan      1600      TRUE
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39631536
Sorry do not understand why you need this column TRUE false ? did you see the file I posted ?
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39632799
Thank you for the points but do not understand why you awarded my question as a solution but not the real solution. I also do not understand you last comments about the column of TRUE as you did not answer it.

Please feel free to revert as if the solution is not adequate for you I am willing to change it. We are here to provide solution that are workable not just to collect points and leave you unsatisfied.

I would appreciate your comments.
gowflow
0
 

Author Comment

by:Rayne
ID: 39633663
Hello gowflow, sure I will comment on this question in a while.
Here is another one if you are interested :)
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_28289157.html
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now