Named ranges interfere with simple formulas

[Guest]

I am my own worst enemy. I created a macro that utilizes named ranges when
it creates spreadsheets. If my users want to create their own formula and
inadvertently use my named range, the results are incorrect.

A simple formula they'd put in such as =d3/c3, becomes =d3/RoysNamedRange.
of course, when they copy this formula down a column, =d4/RoysNamedRange is
not a relative reference and is incorrect.

What can I suggest?

 

[Dave Peterson]

Do your names have to be visible to the user?

If no, then maybe your macro that creates that name can also make the name not
visible.

It seemed to work ok in my simple tests.

This is how I did it:

ActiveSheet.Range("D3").Name = "MyRng"
ActiveWorkbook.Names("MyRng").Visible = False

or
ActiveWorkbook.Names.Add Name:="myRng", _
RefersTo:=Worksheets("sheet1").Range("d3"), Visible:=False

 

[Guest]

That's a wonderful idea, thank you