Using offset and pivot table with refresh

Hello,
I have a range. Like listed below:
Code:

range1=
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$100000),1)

lets say the data is

Title
1
2
3
4

I then create a pivot table from range1

If I change 2 to aa then refresh the pivot table everything is great.

However if I add a 5 to the list, refresh, it does not update.
Is there a way to make this work?

thanks

Hi Magnus,

Please have a look at the attached file.

Hope this helps you.

Kind regards - Martin