Removing range names in formulae

[Gary Baker]

If I have a spreadhseet with a lot of formulae that use
range names (eg Apples + Pears + Oranges), is there a
simple way to change all the range names back to the cell
referencs to which they refer (eg B7 + A5 + C3?

Thanks

 

[Dave Peterson]

Jim Rech offered this solution:
http://groups.google.com/groups?threadm=u3ZAo#FmAHA.2048@tkmsftngp03

 

[Mark E. Philpot]

The calculation still appears in the function box and the
name "Pears" appears in the Name box.
you can delete the named ranges without affecting the
calculations as they are only tags.
On the Toolbar > Insert > Name > Define then press delete.
This will revert the name back to its cell range.


regards
Mark

 

[Charles Williams]

Unfortunately Jim's very clever solution only works for formulae that refer
to simple names that refer to the sheet that contains the formula.

John Walkenbach's PUP utility works for simple absolute names.

I have not yet found a solution that works in all cases (especially when a
name is constructed using INDIRECT!).


--
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm