Dynamic Data Source Not Working

[clowpow76]

I'm using a Dynamic Data Source for a Pivot Table, and for some reason it isn't caputing all of my data....it is only capturing 1431 rows of data and I have 1495. Formula I'm using is =OFFSET('Store Delivery Issues'!$A$1,0,0,COUNTA('Store Delivery Issues'!$A:$A),13)

What would cause it to not capture my last 64 rows of data? Cells are formatted exactly the same?

 

[Derek Brown]

Hello and welcome to The Board.
It may be useful to know which version of Excel is being used.
Is the pivot table being refreshed?
I think that the 'dynamic range' would only be used when creating or refreshing the pivot cache?

 

[clowpow76]

I'm using 2007. The Pivot Table is being refreshed automatically. When I go into the "Name Manager" under formulas and view my dynamic source, the range isn't including all of my data, but it is capturing 1431 of 1495 lines which is what I can't figure out?

 

[Derek Brown]

Could there be blank cells in that Column A range, that would not be counted by COUNTA?

 

[clowpow76]

No blank cells in column A.

 

[Derek Brown]

The formula is good - it works for me.
What name are you giving to that range?
Not sure what you meant by "When I go into the "Name Manager" under formulas and view my dynamic source, the range isn't including all of my data" because you should only see the formula in Name Manager.
What happens if you use Alt+E+G, type in your Range Name and press ENTER? It should select the dynamic range.

 

[clowpow76]

I named the range "PIVOT". In the Name Manager, if you click on the formula itself, it will show you the selected range. If I use Alt+E+G, type in the Range Name and press ENTER the same thing happens, it only selects a portion of my data.

 

[Derek Brown]

What value do you get if you enter:
=COUNTA('Store Delivery Issues'!$A:$A)
in a cell?

 

[clowpow76]

I get 1443 even though there are 1508 rows of data (I've added more rows since my initial posting which is why these numbers have changed.)

 

[Derek Brown]

That is telling me that you have 65 blank cells in Column A.
Use the COUNTBLANK function for that range to see if there are any.
=COUNTBLANK(A1:A1508)
or in a copy of your workbook, create a new column B and enter the following formula in B1:
=IF(COUNTA($A$1:A1)<>ROW(),"X","")
Copy-down the formula and look for the "X" in column B.