updating pivot tables using dynamic data source

[Guest]

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]

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.

 

[Guest]

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]

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)