Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

pivot on visible

Posted on 2013-11-06
7
Medium Priority
?
229 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 31

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 31

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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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 31

Accepted Solution

by:
gowflow earned 2000 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 31

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

926 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