Named ranges interfere with simple formulas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RoyWollen - 30 Sep 2006 19:50 GMT

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?

Reply to this Message

Dave Peterson - 30 Sep 2006 20:48 GMT

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

> 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
[quoted text clipped - 5 lines]
>
> What can I suggest?

Signature

Dave Peterson

Reply to this Message

RoyWollen - 30 Sep 2006 22:53 GMT

That's a wonderful idea, thank you

Reply to this Message