Forum Discussion

clh_1496's avatar
clh_1496
Brass Contributor
Jul 21, 2022

Dynamic named range with Pivot table

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

 

excel
Formulas and Functions

2 Replies

Sort By
  • Martin_Weiss's avatar
    Martin_Weiss
    Bronze Contributor
    Jul 21, 2022

    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.

Resources