DYNAMIC RANGE FOR PIVOT TABLE

S

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top