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. |
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. |
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 |
All times are GMT +1. The time now is 11:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com