Dynamic range for columns in a pivot table

[ariel20029]

Hi all,
I am trying to create a dynamic range to accommodate when the number of rows and columns can change in the data set when creating a pivot table. I created a named range but I cant quite get it to work. Any help please..

=OFFSET('Waterfall 23'!$A$2,0,0,COUNTA(Waterfall '23'!$A:$A)1,1)

 

[sadboy309]

You use Menu Formulas > Name Manager > New > Name: LastRowA ; Refers To: =OFFSET('Waterfall 23'!$A$2,0,0,COUNTA(Waterfall '23'!$A:$A),1) > OK > Close.

So, you have a Name LastRowA to Address Last Range on column 'A'. And, it also is Address [$A$2:$A$...].

 

[ariel20029]

Will this change the if the number of columns change as well and not just the rows?
thanks,
Sharon