Dynamic named range with Pivot table

[clh_1496]

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:

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]

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.

[#3]

@clh_1496 

Structured table shall work, it expands data source automatically.