Dynamic Pivot Table Probems

**db1966** · 08-28-2008, 10:58 AM

Here goes - this might take some explaining:-

I have a spreadsheet the includes data validation, auto fill fields using formulas and conditional formatting all over the place. The user will be adding rows of data whenever applicable, so I want to create a dynamic pivot table that will update automatically. I think I've done it properly, Insert - Name - Define. In the Refers To field, I've added:-

=OFFSET(Sheet1!$A$10,0,0,COUNTA(Sheet1!$A:$A),12)

I've then based the Pivot Table on the defined name. For some reason, when I add a new row of data, the pivot table doesn't update, I have to do it manually. When I go back into Sheet1 and click in the Refers To field, there is an extra "blank" row showing as included in the data range (screen print attached) Is my OFFSET wrong, or does it have anything to do with the fact each row (down to line 300) contains formulas and #N/A's? I've used this before and can't see what I'm doing differently, other than the aforementioned formulas etc.

Any help/advise appreciated

**ptm0412** · 08-28-2008, 11:58 AM

Is there anything in any cell in A1:A9? Or A20:A65000?

**db1966** · 08-29-2008, 03:46 AM

Yes, A1:A9 has various headers/titles, not related to any of the calcs I'm trying to do, they're just cosmetic. Not sure how to word this, but A20:A300 are all linked to a data validation list, which is the same for columns D, G, H, I & L. Columns B, C & J all contain formulas - B & C are linked to whatever the user selects in A, and J is calculated based on the selection in column I. In all cases, the formulas/validation goes to Row 300. There's nothing in any of the other rows.

**ptm0412** · 08-29-2008, 09:23 PM

I think you may try
=OFFSET(Sheet1!$A$10,0,0,COUNTA(Sheet1!$A$10:$A$300),12) instead

**db1966** · 08-30-2008, 03:18 AM

No joy I'm afraid, I still need to refresh the pivot table. Thanks for the suggestion though.

**VBA Noob** · 08-30-2008, 04:21 AM

How can anyone see the problem when you don't attach a example file to see what your doing wrong.

Why post a word document to a excel forum ??

PS. I suspect your sheet name isn't sheet1

VBA Noob

**db1966** · 08-30-2008, 07:04 AM

The reason for the word document is because I wanted to show a screenshot and that was the easiest way I could find. The actual workbook contains confidential data which I can't post on a public forum, but I've removed it and attached a "blank" copy.

PS - the sheet is called Sheet1.

**ptm0412** · 08-30-2008, 10:40 AM

Yes, the Pivot Table cannot be autorefreshed, we have to click refresh button, even if you simply change the available data (for instance, change an amount from 400 to 420).

Anyway, your PivData name is refered to an absolute column but row relative reference:
=OFFSET(Sheet1!$A$10;0;0;COUNTA(Sheet1!$A10:$A300);12)
So it changes according to your current selective cells. It must be all absolute (row, column) like $A$10:$A$300

And, you still must press the refresh button. The different is it refreshes to the exact datasource.

However, you could make a macro like this to force the PivotTable to be refreshed any time you change or add data in column A or column F (Amount):

PHP Code:


Private Sub Worksheet_Change(ByVal Target As Range) 
If Target.Column = 1 Or Target.Column = 6 Then 
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh 
End If 
End Sub

**ptm0412** · 09-01-2008, 08:06 AM

I think last time you used CountA($A:$A) and it worked, because you didn't have tittle. Now you have a tittle in A5, then CountA return a number of 1 unit larger, lead to adding 1 blank row below your source data. You can use CountA($A:$A)-1 also.

But when you are standing on cell A10 and define the name with relative reference like CountA($A10:$A300), then it will change. For example, if you now stand on cell any cell in row 9, your Name will refered to $A9:$A299. If you stand on cell A11 or B11, it will refered to $A11:$A301.

So, as I have said, you should defind name to a full absolute reference $A$10:$A$300