Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic range in Pivot table | Excel Discussion (Misc queries) | |||
Using dynamic range to create pivot table | Excel Discussion (Misc queries) | |||
Dynamic Range in a Pivot Table | Excel Discussion (Misc queries) | |||
Crate group of date, with Dynamic Range in pivot table not working | Excel Discussion (Misc queries) | |||
Refreshing a dynamic range variable | Excel Programming |