create a pivot table

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
Euro5Asked:
Who is Participating?
 
Elton PascuaCommented:
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
 
Elton PascuaCommented:
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
 
Euro5Author Commented:
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
 
Patrick MatthewsCommented:
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
 
Euro5Author Commented:
Excellent information, thanks very much!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.