DYNAMIC RANGE FOR PIVOT TABLE

[Simon]

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),COUNTA(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

 

[Roger Govier]

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 source>insert tab>Table>click my Table has
headers.
If there are any untitled columns, Excel will allocate names to them.

Then, from the Design tab>Summarize with Pivot Table