Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Applying Named Ranges to Existing Formulas
I've searched evrywhere for help on this issue but can't seem to get a clear
position. I have a large workbook with thousands of formulas spread across 20+ worksheets. All of these formulae make extensive use of standard assumptions contained on one particular sheet. I would like to apply names to these assumptions (constants) and have the existing formulas use the new name references instead of the absolute cell references that they currently have. It would make reading and following the formulae much easier. I have defined the names but when I try and apply them excel keeps telling me tat it can't find any references to change. Its almost as if the named ranges will only apply to the sheet that they are on - not the whole workbook. The names are defined as workbook in the name scope. Is there something I'm doing wrong - it seems mad that if you name a cell after a formula is written on another worksheet that Excel will not replace the absolute refernce with the named range! Thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Applying Named Ranges to Existing Formulas
It will not change absolute, mixed, or relative references to the new name.
You'll want to use Ctrl+H (the find & replace function) to accomplish this as you continue to identify your constants. -- Please remember to indicate when the post is answered so others can benefit from it later. "DmanDub" wrote: I've searched evrywhere for help on this issue but can't seem to get a clear position. I have a large workbook with thousands of formulas spread across 20+ worksheets. All of these formulae make extensive use of standard assumptions contained on one particular sheet. I would like to apply names to these assumptions (constants) and have the existing formulas use the new name references instead of the absolute cell references that they currently have. It would make reading and following the formulae much easier. I have defined the names but when I try and apply them excel keeps telling me tat it can't find any references to change. Its almost as if the named ranges will only apply to the sheet that they are on - not the whole workbook. The names are defined as workbook in the name scope. Is there something I'm doing wrong - it seems mad that if you name a cell after a formula is written on another worksheet that Excel will not replace the absolute refernce with the named range! Thanks in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Applying Named Ranges to Existing Formulas
THanks for the help. Seems like a deficiency in Excel. I really would have
thought that this is the type of thng that would often happen and should be easy to accomodate. It works on the sheet that the names are defined on so why not on other sheets. Anyway, thanks again for the clear answer - at least now I know what I have to do. "KC Rippstein" wrote: It will not change absolute, mixed, or relative references to the new name. You'll want to use Ctrl+H (the find & replace function) to accomplish this as you continue to identify your constants. -- Please remember to indicate when the post is answered so others can benefit from it later. "DmanDub" wrote: I've searched evrywhere for help on this issue but can't seem to get a clear position. I have a large workbook with thousands of formulas spread across 20+ worksheets. All of these formulae make extensive use of standard assumptions contained on one particular sheet. I would like to apply names to these assumptions (constants) and have the existing formulas use the new name references instead of the absolute cell references that they currently have. It would make reading and following the formulae much easier. I have defined the names but when I try and apply them excel keeps telling me tat it can't find any references to change. Its almost as if the named ranges will only apply to the sheet that they are on - not the whole workbook. The names are defined as workbook in the name scope. Is there something I'm doing wrong - it seems mad that if you name a cell after a formula is written on another worksheet that Excel will not replace the absolute refernce with the named range! Thanks in advance |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Applying Named Ranges to Existing Formulas
I think they do this for two reasons:
1) they hate to assume this is what you want...what if it isn't?? There are valid reasons why you might not want existing references changed, and most people setup named ranges during the initial design (although designing on the fly is common). 2) once you create the name, every time you enter a formula and use your mouse to select that range, it DOES substitute the name for the range in your formula (but you can overwrite that with the physical addresses if desired). So it does what you want going forward but just doesn't retroactively make assumptions. 3) it's usually no big deal. Find & Replace is so easy to use and extremely fast. As you create each name just do a Find & Replace while you're at it...the only time it's a headache is if you refer to it on more than a handful of sheets, in which case using a macro to do the F&R for each sheet makes more sense. Cheers! -- Please remember to indicate when the post is answered so others can benefit from it later. "DmanDub" wrote: THanks for the help. Seems like a deficiency in Excel. I really would have thought that this is the type of thng that would often happen and should be easy to accomodate. It works on the sheet that the names are defined on so why not on other sheets. Anyway, thanks again for the clear answer - at least now I know what I have to do. "KC Rippstein" wrote: It will not change absolute, mixed, or relative references to the new name. You'll want to use Ctrl+H (the find & replace function) to accomplish this as you continue to identify your constants. -- Please remember to indicate when the post is answered so others can benefit from it later. "DmanDub" wrote: I've searched evrywhere for help on this issue but can't seem to get a clear position. I have a large workbook with thousands of formulas spread across 20+ worksheets. All of these formulae make extensive use of standard assumptions contained on one particular sheet. I would like to apply names to these assumptions (constants) and have the existing formulas use the new name references instead of the absolute cell references that they currently have. It would make reading and following the formulae much easier. I have defined the names but when I try and apply them excel keeps telling me tat it can't find any references to change. Its almost as if the named ranges will only apply to the sheet that they are on - not the whole workbook. The names are defined as workbook in the name scope. Is there something I'm doing wrong - it seems mad that if you name a cell after a formula is written on another worksheet that Excel will not replace the absolute refernce with the named range! Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove named ranges from formulas?? | Excel Discussion (Misc queries) | |||
Formulas in arrays and named ranges | Excel Discussion (Misc queries) | |||
Named Ranges in Array Formulas | Excel Worksheet Functions | |||
Applying range names to existing formulas in separate worksheets | Excel Discussion (Misc queries) | |||
Named ranges interfere with simple formulas | Excel Discussion (Misc queries) |