Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Expand table with a row when pivot updates and expands

  1. #1
    New Member
    Join Date
    Apr 2015
    Location
    Copenhagen, DK
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Expand table with a row when pivot updates and expands

    Hello,
    I have been looking for answer in the forum but can't seem to find it, or maybe can't formulate search properly. In any case, I would appreciate your help with automating the excel sheet. It is not a formula specific question, so if you have a better workaround for the whole thing let me know.

    I have a .xls document measuring KPIs:
    1. it contains 5 data entry sheets, where data is posted in every week.
    2. then I use pivots to filter/cutomise what I need to measure and the pivot data is referred to in normal tables,
    3. which in the end are connected to graphs.

    I can't use graphs straight from pivots because majority require additional calculations which I make in normal tables. The problem that I am facing is that whenever I add new information every week and update the pivot tables, the normal tables do not expand automatically and I need to drag the table down to include the latest row. This is what I would like to automate, that IF Pivot table gets new row, it will be included in normal table automatically without the need for extra steps.

    Let me know if you have any ideas. If possible to do it with formulas that would be great, otherwise VBA is ok too.

    [Excel 2013, Windows 7]

  2. #2
    Board Regular Trevor G's Avatar
    Join Date
    Jul 2008
    Location
    Tamworth, Staffordshire
    Posts
    6,262
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Expand table with a row when pivot updates and expands

    You can use Dynamic ranges to a pivot table by using Named Ranges and offsetting formula. Check out this link

    http://excelpivots.com/excel/pivot_t...g_data_ranges/
    Trevor
    I am on a learning curve of life, I know a little but like to share what I have learnt with others.
    I am using Microsoft Office 2003 to 2016
    Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.

  3. #3
    New Member
    Join Date
    Apr 2015
    Location
    Copenhagen, DK
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Expand table with a row when pivot updates and expands

    Hi Trevor,
    thanks for reply. I can't use the dynamic ranges for pivot as there are further calculations after I get data from pivots. The columns in the table connect the data from pivot and tables from other sheets with VLOOKUP and then calculations between the columns are made to create the data to be shown in the graphs.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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