Refreshing a Pivot Table from a dynamic range
 

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

--
matp
-----------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...fo&userid=2107
View this thread: http://www.excelforum.com/showthread.php?threadid=47787

Refreshing a Pivot Table from a dynamic range
 

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.

--
Regards,
Tom Ogilvy


"matpj" wrote in
message ...

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

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

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

can anyone help?


--
matpj
------------------------------------------------------------------------
matpj's Profile:
http://www.excelforum.com/member.php...o&userid=21076
View this thread: http://www.excelforum.com/showthread...hreadid=477871

Refreshing a Pivot Table from a dynamic range
 

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

"matpj" wrote:


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

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

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

can anyone help?


--
matpj
------------------------------------------------------------------------
matpj's Profile: http://www.excelforum.com/member.php...o&userid=21076
View this thread: http://www.excelforum.com/showthread...hreadid=477871