Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Query and expanding named ranges | Excel Discussion (Misc queries) | |||
Importing Excel named ranges using MS Query | Excel Discussion (Misc queries) | |||
AHHHH-Get Data from Multiple Excel workbooks | Excel Discussion (Misc queries) | |||
Viewing List of Named Ranges | Excel Discussion (Misc queries) | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |