Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    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

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 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

  3. #3
    2 Star Lounger
    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...

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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>


    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    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

  6. #6
    Plutonium Lounger
    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

  7. #7
    2 Star Lounger
    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.

  8. #8
    Plutonium Lounger
    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.

  9. #9
    Plutonium Lounger
    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.

  10. #10
    2 Star Lounger
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •