pivot on visible

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
RayneAsked:
Who is Participating?
 
gowflowConnect With a Mentor Commented:
Sorry do not understand why you need this column TRUE false ? did you see the file I posted ?
gowflow
0
 
gowflowCommented:
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
 
gowflowCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
RayneAuthor Commented:
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
 
RayneAuthor Commented:
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
 
gowflowCommented:
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
 
RayneAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.