Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
remove named ranges from formulas??
I have a workbook that has a lot of formulas that use named ranges.
Is there a way to remove the named ranges and have the formulas show conventional cell references? Not sure if there is an option in Excel or this requires a macro? I'm using XL 03. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
remove named ranges from formulas??
Hi,
Range names are good things, that said, if you delete the range names all your formulas will return errors. You can use the Replace command to replace all references to a name with its address. With a lot of range names you should consider a VBA macro. Cheers, Shane Devenshire "Dave F" wrote: I have a workbook that has a lot of formulas that use named ranges. Is there a way to remove the named ranges and have the formulas show conventional cell references? Not sure if there is an option in Excel or this requires a macro? I'm using XL 03. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
remove named ranges from formulas??
I'd do this against a copy of the file...
Jim Rech posted a nice response at: http://groups.google.com/groups?thre...%40tkmsftngp03 From: Jim Rech ) Subject: Can I "De-Name" Formula Cell References? Newsgroups: microsoft.public.excel.misc, microsoft.public.excel Date: 2001-02-16 13:32:51 PST To do it to a cell or two first turn on Transition Formula Entry under Tools, Options, Transition. Then go to the cell and press F2 and Enter. When you turn off TFE the formula references should be de-named. If you have a lot of cells to de-name select the range and run this macro: Sub Dename() Dim Cell As Range ActiveSheet.TransitionFormEntry = True For Each Cell In Selection.SpecialCells(xlFormulas) Cell.Formula = Cell.Formula Next ActiveSheet.TransitionFormEntry = False End Sub -- Jim Rech Excel MVP ==== Be aware that any reference to those names in your code will be broken. Dave F wrote: I have a workbook that has a lot of formulas that use named ranges. Is there a way to remove the named ranges and have the formulas show conventional cell references? Not sure if there is an option in Excel or this requires a macro? I'm using XL 03. Thanks. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas in arrays and named ranges | Excel Discussion (Misc queries) | |||
Named Ranges in Array Formulas | Excel Worksheet Functions | |||
Data Validation 2003, Named Ranges/Formulas | Excel Worksheet Functions | |||
building formulas that change frequently using named cell ranges | Excel Worksheet Functions | |||
Named ranges interfere with simple formulas | Excel Discussion (Misc queries) |