Prev: Delete OLEObject from Row or Cell
Next: Opening file by partial name in same folder as master file
From: Simon on 29 Mar 2010 00:38
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
From: Roger Govier on 29 Mar 2010 04:45
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

--
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),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
 | 
Pages: 1
Prev: Delete OLEObject from Row or Cell
Next: Opening file by partial name in same folder as master file