Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

pivot on visible

Posted on 2013-11-06
7
Medium Priority
?
225 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

688 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