Applying Named Ranges to Existing Formulas

D

DmanDub

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
 
K

KC Rippstein hotmail com>

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.
 
D

DmanDub

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.
 
K

KC Rippstein hotmail com>

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top