Results 1 to 10 of 10
Thread: Pivot Table adjacent cells (XP)

20061013, 11:12 #1
 Join Date
 May 2002
 Location
 Bristol, Avon, England
 Posts
 110
 Thanks
 0
 Thanked 0 Times in 0 Posts
Pivot Table adjacent cells (XP)
I have a Pivot table and in the columns adjacent to it I have calculations based on the rows in the pivot table.
As the background data base grows more rows get added to the pivot table. Is there a way to automatically get the calculated rows in the adjacent columns to 'grow' in sync with the pivot table?
There is a totals row at the bottom of the pivot table and the adjacent columns which also needs to grow in sync

20061013, 12:04 #2
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,930
 Thanks
 139
 Thanked 502 Times in 478 Posts
Re: Pivot Table adjacent cells (XP)
Have you thought about incorporating the calculations as 'calculated fields' within the pivot table itself??
Or, if you have some idea about the maximum number of pivot rows expected, copy the formulas down to cover that number of rows.
If you don't want to display any results for 'phantom' pivot rows, you could adjust your formulas accordingly to display blanks if there is no data in the corrsponding row.
Or, with VBA, you could detect specifically how many pivot rows there are (say, after a pivot refresh), and then copy the formulas to match the specific pivot row range.
zeddy

20061013, 14:22 #3
 Join Date
 May 2002
 Location
 Bristol, Avon, England
 Posts
 110
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Pivot Table adjacent cells (XP)
Hi,
I neglected to mention that one of the columns adjacent to the pivot table requires a user to input data for that row. (this can't be done in the raw data as the whole point of the pivot table is to assign hours worked to a client at a summary level)
The calculated fields option doesn't work because I have to reference fields not in the pivot table.
The second option of having more rows than I need doesn't work as new entries to the Pivot table don't always add to the bottom. A new row may enter in the middle of the table. But the adjacent cells are then out of sync with the pivot table.
I havn't tried the third option as I can't begin to imagine where to start!
Thanks for your help though...

20061013, 15:52 #4
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,284
 Thanks
 3
 Thanked 192 Times in 178 Posts
Re: Pivot Table adjacent cells (XP)
Hi Matt,
We would need more details, or preferably a sample workbook, to come up with exact code, but as an idea if you had this code behind the pivot table's worksheet it would automatically fill down formulas in the column to the right of the pivottable whenever the pivot table is refreshed:
<pre>Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
With Target.DataBodyRange
.Columns(.Columns.Count).Offset(0, 1).FillDown
End With
End Sub
</pre>
HTHRegards,
Rory
Microsoft MVP  Excel

20061016, 08:28 #5
 Join Date
 May 2002
 Location
 Bristol, Avon, England
 Posts
 110
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Pivot Table adjacent cells (XP)
Rory,
I tried the code but I don't think it worked (I realise it would need to be expanded for all columns but I couldn't get it to work for one)
Attached is a sample of the sheet.
Columns B to H are the pivot table. Columns I and J require user input, ad the remaining columns are calculations.
In a nutshell I want columns I to R to keep up with changes to the pivot table (both new rows to the bottom and new rows inserted in the middle)
All/any suggestions gratefully received.
Thanks

20061016, 09:15 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Pivot Table adjacent cells (XP)
Does this do what you want?
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim r1 As Long
Dim r2 As Long
r1 = Range("A65536").End(xlUp).Row ' last row of pivot table
r2 = Range("H65536").End(xlUp).Row ' last row of added columns
If r2 < r1 Then ' rows added
Range("H" & r2 & ":Q" & (r1  1)).Insert Shift:=xlShiftDown
ElseIf r2 > r1 Then ' rows removed
Range("H" & r1 & ":Q" & (r2  1)).Delete Shift:=xlShiftUp
End If
' update formulas
Range("J6:Q" & (r1  1)).FillDown
Range("H" & r1 & ":P" & r1).FormulaR1C1 = _
"=SUM(R6C:R<!t>[1]<!/t>C)"
End Sub

20061016, 10:12 #7
 Join Date
 May 2002
 Location
 Bristol, Avon, England
 Posts
 110
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Pivot Table adjacent cells (XP)
Hans
This is 90% there. The calculated columns work perfectly.
There is still a problem when new rows get inserted into the pivot table, the hours entered in columns H and I don't get shifted down. As there is no way to know where a new line will be inserted I can't think of a way to solve this. It will be even more difficult if more than one new row gets inserted as they may not be consecutive rows.

20061016, 10:29 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Pivot Table adjacent cells (XP)
I agree that will be difficult to handle. The PivotTableUpdate event occurs after the fact, so you have no idea which data in columns H and I belong where, since they are manually entered data, not formulas. It would be best to convert the data in columns H and I to some kind of formulas that use the company name and business unit in columns A and B to look up the values in a separate table. I'll have to think about that.

20061016, 14:15 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Pivot Table adjacent cells (XP)
I tried to do it with a lookup function. Although I got the lookup to work, this made the PivotTableUpdate event procedure fail for some reason I can't discover.
In the attached workbook, the extra data for Operators and Supervisor have been added to the source data. Each figure has to be added only once; the corresponding data fields in the pivot table use MAX.
The macro doesn't have to deal with columns H and I any more.

20061016, 14:33 #10
 Join Date
 May 2002
 Location
 Bristol, Avon, England
 Posts
 110
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Pivot Table adjacent cells (XP)
Hans
Thank you. I think that that is a workable solution. And less to go wrong than using lookups etc.