Hello.

Say I have a name range (test) formula below.

=Data!$A$1:INDEX(Data!$A:$A,MATCH(Sheet1!$Z$1,Data!$A:$A,0))
And when this formula was created, the 'scope' is under Workbook instead of Sheet1

Now say that I make a copy of Sheet1 into Sheet2, the named range will get 'copy' but with the scope as 'Sheet2' instead of 'Workbook'.

My question now is that, is there a way to have the original dynamic range to look at the current active sheet.

I'm thinking of like this

=Data!$A$1:INDEX(Data!$A:$A,MATCH(CurrentActiveSheet!$Z$1,Data!$A:$A,0))
So that this way, whenever I make copies of Sheet1, I don't get a list of named range