Pivot Table - Using a Dynamic Range

[BlueRhinos]

Because my pivot table is used by several users the data source changes often. In response, I created and applied a dynamic range using the offset formula. While the pivot talbe grabs the correct # of records, it inserts a blank column and row. There are NOT any blank cells in the data source range, as reflected by pivot table counts for the Blank column and Row. Any idea why Blank is showing up in my pivot table and how I can correct while maintaining the dynamic range?

Thanks!

BlueRhinos

 

[GlennUK]

Check that your dynamic range is pointing to the expected region. In the Name/Define dialog, choose the name, and click on the Refers To: selection button, and the region covered will be surrounded by the marquee ( the dotted line, the marching ants, selection indicator ).

 

[BlueRhinos]

Glenn, Thanks...using your advice, I was able to see that the expected region was broader than anticipated. This was due to the fact that I want the dynamic range (offset) formula to only grab records where there's a value in the cell. it appears to be grabbing records that also have formulas built in a column but that come back blank. Any ideas on how I can keep the dynamic range, but tell it to only grab records where the cell is populated with a value? Thanks!

-bluerhinos

 

[GlennUK]

Can you show the formula that you're using for the dynamic range definition. When you say "broader" ... do you mean that your data can be a variable number of columns?

 

[BlueRhinos]

Hi Glenn,

Here's my offset formula-
=OFFSET(ReportTab!$A$1,0,0,COUNTA(ReportTab!$A:$A),COUNTA(ReportTab!$1:$1))

When I say broader, I mean that the expected region contained more records than I had expected. I think the root cause is that the first column is formula driven and if the formula produces a blank cell, the offset formula captures it and includes it in the expected region.
My hope is that there's a way to modify the formula...any thoughts?

BlueRhinos

 

[RoryA]

If you have no gaps in the column, you can use COUNTIF(ReportTab!$A:$A,"<>") instead of COUNTA

 

[GlennUK]

Did Rory's suggestion work for you?

 

[BlueRhinos]

Glenn/Rory,

I updated the height part of the formula with Rory's countif suggestion...but it still seems to be including more rows than it should...basically any rows (cells) with a formula. we need it to count the row only if the cell has a data value...thoughts? I apprecate all the help you guys provide.

BlueRhinos

 

[BlueRhinos]

Glenn/Rory,

My next thought as to nest an istext function within the countif...don't think this is possible. Is there syntax that would be allow us to count a cell if it started with a letter? This would ensure the count excluded cells with formulas.

 

[GlennUK]

Glenn/Rory,

My next thought as to nest an istext function within the countif...don't think this is possible. Is there syntax that would be allow us to count a cell if it started with a letter? This would ensure the count excluded cells with formulas.

Yes, how about =COUNTIF(A:A,"=?*")