Making Dynamic Name Range more dynamic

Hello.

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

So that this way, whenever I make copies of Sheet1, I don't get a list of named range

Hi there,

To overcome the problem you describe, you should create the first defined Name at WorkSHEET (not WorkBOOK) level. If you use this approach the defined Name will be copied correctly when you make a copy of the worksheet.

Hope this helps.

Regards,

Greg M