Pivot Range Dynamic by Column & Row Length

[wmp8b79]

Hello,

I am hoping that someone can tell me why I am able to attach a pivot table to the named range "PivotRange1" and not to "PivotRange2". While in Name Manager they produce same range so I don't understand why I get a message "Range not valid" for PivotRange2 and PivotRange1 works just fine.

Here's what I have coded in for the named range of each

PivotRange1: =INDIRECT("Sheet1!A1:R"&COUNTA(Sheet1!$C:$C))

this produces a dynamic range by row for a pivot table but column length is static to column R

Pivot Range2: =INDIRECT("Sheet1!A1:"&ADDRESS(COUNTA(Sheet1!$C:$C),COUNTA(Sheet1!$1:$1),4))

this produces a dynamic range by both row and column but returns a range not valid error when I try to attach the pivot table

Thanks for the help!

 

[wmp8b79]

I still would like help with this, does anyone have a suggestion or an idea why the second named formula does not attach to a pivot table?

 

[wmp8b79]

Ended up finding a better way to do a dynamic range by row and column to attach to a pivot... here's the formula to attach to a named range

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))