Refreshing a Pivot Table from a dynamic range

  • Thread starter Thread starter matpj
  • Start date Start date
M

matpj

Hi there,
I have a pivot table on one worksheet, that is refreshed from a rang
of data on a second worksheet.

the data is refreshed using a query, so can contain different number
of rows.

how can I code it to always refresh using the complete range from th
data worksheet?

can anyone help
 
Is there a defined name for the table, something like External_Range1 or
something close to that.

Use that name as the source for you pivot table.
 
If you use a named range (Insert --> Name --> Define) and under 'Refers to'
use the offset formula to define a range (eg:
=OFFSET(Data!$A$1,0,0,MAX(2,COUNTA(Data!$A:$A)),11) - the max statement
ensures that if your data range returns no data, the pivot table will still
work. Look under Help for details of how to use the offset function properly)

Then use the named range as your pivot table source data.

This assumes that you are using formulas at the side of the query to process
the data further. If all of your data is in the query, then you can use the
name of the query as the data range for the pivot table.

To have everything automatically update when you refresh the query, you need
to disable the background refresh in the query (under data range properties
--> Refresh control) , and in table options on the pivot table, check the box
by 'refresh on open'

I hope that helps!

Richard
 
Back
Top