Solved

pivot on visible

Posted on 2013-11-06
7
211 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

920 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

15 Experts available now in Live!

Get 1:1 Help Now