Named ranges interfere with simple formulas
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? |
Named ranges interfere with simple formulas
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 RoyWollen wrote: 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 |
Named ranges interfere with simple formulas
That's a wonderful idea, thank you
"RoyWollen" wrote: 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? |
All times are GMT +1. The time now is 02:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com