Dynamic named range with Pivot table




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:



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?




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.


Structured table shall work, it expands data source automatically.