I have a workbook with many cells with names. Throughout
the workbook the references to these cells are made via
the "name".

I want to covert all of these references to the physical
cell address vs. the name. Are there any clever ways to
do this other than one by one?

I have a worksheet that contains all the named cells in
the workbook and their physical address. I tried writing a
macro that did a find and replace. The find worked but the
replace did not. It does work manually.. but there are too
many to convert manually.

Also, are there any utilities that will provide a list of
all cells dependent upon a given cell.

Does this help?

Sub ReplaceName()
Cells.Replace What:="badname", _
Replacement:="$G$4", LookAt:=xlPart
End Sub

--
Don Guillett
SalesAid Software

"Mike Short" wrote in message
...
I have a workbook with many cells with names. Throughout
the workbook the references to these cells are made via
the "name".

I want to covert all of these references to the physical
cell address vs. the name. Are there any clever ways to
do this other than one by one?

I have a worksheet that contains all the named cells in
the workbook and their physical address. I tried writing a
macro that did a find and replace. The find worked but the
replace did not. It does work manually.. but there are too
many to convert manually.

Also, are there any utilities that will provide a list of
all cells dependent upon a given cell.

This is a macro I wrote to "de-name" formulas in a selected range several
years ago. By de-name I mean replace all names with actual cell addresses,
not particular names. Frankly I haven't needed it much and I never tested
it much. It seems to work in the simple cases I've used it and you're
welcome to try it but if it breaks down in particular circumstances I
wouldn't be surprised.

--
Jim Rech
Excel MVP

Sub DenameFormulas()
Dim Cell As Range
ActiveSheet.TransitionFormEntry = True
With Selection
If .Areas.Count 1 Then
For Each Cell In .SpecialCells(xlFormulas)
Cell.Formula = Cell.Formula
Next
Else
Selection.Formula = Selection.Formula
End If
End With
ActiveSheet.TransitionFormEntry = False
End Sub

If you want to do all at once. Be careful that it doesn't replace names you
don't want replaced.

Sub ReplaceNamesWithAddress()
For Each ws In Worksheets
ws.Select
For Each n In ActiveWorkbook.Names
x = Right(n.RefersTo, Len(n.RefersTo) - 1)
Cells.Replace what:=n.Name, replacement:=x, _
Lookat:=xlPart
Next n
Next ws
Sheets("sheet1").Select
End Sub

--
Don Guillett
SalesAid Software

"Mike Short" wrote in message
...
I have a workbook with many cells with names. Throughout
the workbook the references to these cells are made via
the "name".

I want to covert all of these references to the physical
cell address vs. the name. Are there any clever ways to
do this other than one by one?

I have a worksheet that contains all the named cells in
the workbook and their physical address. I tried writing a
macro that did a find and replace. The find worked but the
replace did not. It does work manually.. but there are too
many to convert manually.

Also, are there any utilities that will provide a list of
all cells dependent upon a given cell.

This will do it automatically for all workbook names over all sheets.

Sub ReplaceName()
Dim nme As Name
Dim sh As Worksheet
For Each nme In ActiveWorkbook.Names
For Each sh In ActiveWorkbook.Worksheets
sh.Cells.Replace _
What:=nme.Name, _
replacement:=Replace(Names(nme.Name).RefersTo, "=", ""), _
LookAt:=xlPart
Next sh
Next nme
End Sub


It will fail if you have worksheet names though.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Don Guillett" wrote in message
...
Does this help?

Sub ReplaceName()
Cells.Replace What:="badname", _
Replacement:="$G$4", LookAt:=xlPart
End Sub

--
Don Guillett
SalesAid Software

"Mike Short" wrote in message
...
I have a workbook with many cells with names. Throughout
the workbook the references to these cells are made via
the "name".

I want to covert all of these references to the physical
cell address vs. the name. Are there any clever ways to
do this other than one by one?

I have a worksheet that contains all the named cells in
the workbook and their physical address. I tried writing a
macro that did a find and replace. The find worked but the
replace did not. It does work manually.. but there are too
many to convert manually.

Also, are there any utilities that will provide a list of
all cells dependent upon a given cell.