DYNAMIC RANGE FOR PIVOT TABLE
 

Hi I am using Excel 2007 and trying to create a dynamic range for a pivot
table so that its source adjusts depending on the number of rows in the
xternal workbook source.

I have created a range "SalesDataset" with the following formula:

=OFFSET(Summary!$A$1,0,0,COUNTA(Summary!$A:$A),COU NTA(Summary!$1:$1))

However when I try to change the Pivot Table data source to =SalesDataset I
get a pop up "Reference not valid"

Can anyone steer me in the right direction.

Many thanks
Simon

DYNAMIC RANGE FOR PIVOT TABLE
 

Hi Simon

There is nothing wrong with your formula (although I prefer to use Index
rather than Offset).
However, if your titled columns are not contiguous, and you have any gap
with an untitled column, then you will get that message.

With XL2007, you would probably be better using the Table facility,
rather than creating a dynamic range. The table will grow with your data
source automatically.

Place cursor within your data sourceinsert tabTableclick my Table has
headers.
If there are any untitled columns, Excel will allocate names to them.

Then, from the Design tabSummarize with Pivot Table

--
Regards
Roger Govier

Simon wrote:
Hi I am using Excel 2007 and trying to create a dynamic range for a pivot
table so that its source adjusts depending on the number of rows in the
xternal workbook source.

I have created a range "SalesDataset" with the following formula:

=OFFSET(Summary!$A$1,0,0,COUNTA(Summary!$A:$A),COU NTA(Summary!$1:$1))

However when I try to change the Pivot Table data source to =SalesDataset I
get a pop up "Reference not valid"

Can anyone steer me in the right direction.

Many thanks
Simon