Microsoft Excel - Auto extending a table that links to 3 pivot tables in Excel 2010

Asked By Jo on 09-May-12 07:11 AM
Earn up to 50 extra points for answering this tough question.
Hi there

I have 3 separate datasources from which I create 3 separate pivot tables.  I link to these to create one table which I then use to create a line graph.

How do I get the table to update automatically to include the most recent data from the pivot table (how do I get the range to extend downwards automatically) and how can I get the chart range to extend automatically as well?

Thanks!
Jo
Jitendra Faye replied to Jo on 09-May-12 10:46 AM
YOu can use Auto refresh for pivot table.

You can have a pivot table auto-refresh IF, and only if, the pivot table is based on external data

Jo replied to Jitendra Faye on 09-May-12 10:59 AM
Thanks for replying Vickey.  The pivot tables are set to autorefresh and are updating fine.  The table that links to them is the problem as I want it to get longer as the pivots get longer.  I did put in a load of IF(ISNA) statements in but it takes the lines on my graph down to zero and I just want them to show as blanks if there is no data.
Does that make sense?!
wally eye replied to Jo on 09-May-12 12:33 PM
Assuming you are using formulas to link your table to the three pivots, you would need to adjust the formulas to show blanks when there is no pivot table data, then extend the formulas to the maximum range of your pivots.  Short of that, you could use some VBA to re-create the table formulas when you refresh the pivot tables, that is a bit harder but not impossible.

You can use a dynamic range for your chart, that only includes the range with data.  I typically use named ranges for this, something like:

DataRange:

=Offset(DataStart,0,0,Count(A:A),1)

where DataStart is the starting point of a column of the table and column A's rows are fully populated with data in the table.