How to create a dynamic Pivot Table to auto refresh expanding data in Excel?

Hi,

Thanks for your response. Greatly appreciated. I've tried this on several worksheets with no luck. Unless I'm doing something wrong when I insert the table it has never worked upon refreshing the pivot table or pivot chart. With both I get Blank as part of the Pivot chart and table. The quick fix is to go into the pivot table or chart and deselect Blank. Also, I can delete the blank rows which will give me the correct data source range. Too manual. I would rather be able to reference just the data source when it expands or collapses automatically. In my research I have found two ways to do this.

Method 1:

1. I create a name in Name Manager and then paste the following formula in the Refers to: box:

=OFFSET('dynamic pivot with table'!$A$1,0,0,COUNTA('dynamic pivot with table'!$A:$A),COUNTA('dynamic pivot with table'!$1:$1))

2. Then I create my Pivot Table and in the Table/Range box I type the name of the named range that I created in step 1. It works great!

Following is the URL with the complete instructions on how to do this:

https://www.extendoffice.com/documents/excel/5873-excel-create-dynamic-pivot-table.html

Method 2:

(Is something I came up with that also works well):

1. I create a macro that first selects the current region of the Pivot table data source minus the blank rows.
2. Give the selected data source a range name.
3. Refresh the Pivot table or chart.

Hope this helps anyone else with this issue.

John