Expand table with a row when pivot updates and expands

[paxlis]

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]

 

[Trevor G]

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_table_expanding_data_ranges/

 

[paxlis]

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.