updating pivot tables using dynamic data source

I am using Excel Pivot Tables Recipe Book by D. Dalgleish and am in chapter
7, �Updating a Pivot Table�. The data source for the pivots always begins in
cell A10. Column BL is always the last column, but the number of rows will
vary. The data are updated monthly. I followed the steps for naming the
data range using A10 instead of A1 as the starting place, but when I try to
use the named range for the data source for my pivot table, I get an error
message stating �Reference is not valid�. I am using Excel 2003. Any
suggestions?--
dab4211

To create a dynamic range that counts the rows and columns:

=OFFSET(Data!$A$10,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$10:$10))

That assumes there's nothing above or below the date in column A, or to
the right of the data in row 10.

Also, your headings should be in row 10, and there should be an entry in
each heading cell.

dab4211 wrote:
> I am using Excel Pivot Tables Recipe Book by D. Dalgleish and am in chapter
> 7, “Updating a Pivot Table”. The data source for the pivots always begins in
> cell A10. Column BL is always the last column, but the number of rows will
> vary. The data are updated monthly. I followed the steps for naming the
> data range using A10 instead of A1 as the starting place, but when I try to
> use the named range for the data source for my pivot table, I get an error
> message stating “Reference is not valid”. I am using Excel 2003. Any
> suggestions?--
> dab4211


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Thank you. I am still confused. My source data begins in cell A10 because I
placed titles above row 10. I have entries ("headers") in every cell in row
10 identifying the data in each column. I think I will try it without the
titles. Should I begin without anything in the spreadsheet except column
headers?
Again, thanks so much!
--
dab4211


"Debra Dalgleish" wrote:

> To create a dynamic range that counts the rows and columns:
>
> =OFFSET(Data!$A$10,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$10:$10))
>
> That assumes there's nothing above or below the date in column A, or to
> the right of the data in row 10.
>
> Also, your headings should be in row 10, and there should be an entry in
> each heading cell.
>
> dab4211 wrote:
> > I am using Excel Pivot Tables Recipe Book by D. Dalgleish and am in chapter
> > 7, “Updating a Pivot Table”. The data source for the pivots always begins in
> > cell A10. Column BL is always the last column, but the number of rows will
> > vary. The data are updated monthly. I followed the steps for naming the
> > data range using A10 instead of A1 as the starting place, but when I try to
> > use the named range for the data source for my pivot table, I get an error
> > message stating “Reference is not valid”. I am using Excel 2003. Any
> > suggestions?--
> > dab4211
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
>
>

You need a heading in each cell in the first row of the pivot table
source range.

If there is data in column A, above the source data, you can subtract it
in the offset formula:

=OFFSET(Data!$A$10,0,0,COUNTA(Data!$A:$A)-COUNTA(Data!$A$1:$A$9),64)

dab4211 wrote:
> Thank you. I am still confused. My source data begins in cell A10 because I
> placed titles above row 10. I have entries ("headers") in every cell in row
> 10 identifying the data in each column. I think I will try it without the
> titles. Should I begin without anything in the spreadsheet except column
> headers?
> Again, thanks so much!


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html