Dynamic named range with Pivot table

Contributor

Hi,

 

I'm trying to use a dynamic named range with a pivot table so when new data is added, the pivot table expands to capture this, however when I try to change the range to this dynamic named range, it comes up with an error:

clh_1496_0-1658408110090.png

 

The formula used in my named range is:

=OFFSET('Sheet1'!$B$2,0,0,COUNTA('Sheet1'!$B:$B),COUNTA('Sheet1'!$2:$2))

 

Does the data in the named range need to be a table, I've tried it and it didn't make a difference but just thought I'd ask anyway?

 

Charlotte

 

2 Replies

Hi @clh_1496 

 

I really propose to use a formatted table instead of named ranges. It's much simpler and there is no need for OFFSET formulas which could also have an impact on performance in large data sets.

@clh_1496 

Structured table shall work, it expands data source automatically.