?
Solved

create a pivot table

Posted on 2012-09-09
5
Medium Priority
?
696 Views
Last Modified: 2012-09-10
I would like to create pivot that will include all the rows and columns on a given sheet, even if the rows increase/decrease.

On the sample I have included a manual pivot with the needed filter.
However, on refresh, the source data may have changed, so I need it to choose all the data on the source sheet first.
Pivotsample.xlsx
0
Comment
Question by:Euro5
  • 2
  • 2
5 Comments
 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38381434
You are using a table as your data source so it should already dynamically adjust rows and columns as you add them (just refresh the Pivot Table).

Just in case that is not the case, you can create a dynamic range (Formulas > Name Manager) and make it the source of your pivot table.

=OFFSET($A$1,,,COUNTA($A:$A),COUNTA($1:$1))

Open in new window

0
 

Author Comment

by:Euro5
ID: 38381468
techfanatic - The pivot table has a determined range - so it won't add the row or column even on refresh. I really cant find anything formulas>Name Manager that creates a dynamic range....at a loss here.
0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1000 total points
ID: 38381469
Please have a look at my article here:  http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3172-How-to-Automatically-Update-Your-PivotTables-and-PivotCharts.html

It describes techniques no only for automatically updating the source range for a PT, but also for automatically refreshing your PTs.

The named range approach shown above is a viable technique (although I prefer a different formula, as shown in the article).  If you are using Excel 2007 or later, I recommend creating a Table as the data source.
0
 
LVL 8

Accepted Solution

by:
Elton Pascua earned 1000 total points
ID: 38381740
Hi Euro5, check the attached file and go to Formulas > Name Manager. You will see a named range called "mypivotdata". Check the refers to box which contains the offset formula that refers to the entire table.
Pivotsample.xlsx
0
 

Author Closing Comment

by:Euro5
ID: 38383213
Excellent information, thanks very much!!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…

850 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