Map of used names?

[Michael.Tarnowski]

Hi community,
is it possible - and if, how - to get a kind a map i.e. reference,
where defined names are used in a workbook?
Cheers Michael

 

[Mike H]

hi,

Select a cell where you want the list then

Insert|Names|Paste and paste list

Mike

 

[Michael.Tarnowski]

Hi Mike,
thanks for your tip. This gives a list of the definitions, but I'am
looking for a kind of list, in which cell of which sheet a name is
*used*. In other words, which cell is effected if a name definition is
deleted.
Michael

 

[Jarek Kujawa]

surely sb. will come up with a smarter solution but one way might be:

Sub cus()
Dim nazwa As String
Dim adres as String
Dim lista as String
Dim tekst_formuly as String
Dim i as Integer
Dim cell As Range

Dim ws As Worksheet

Application.ScreenUpdating = False
Application.Calculation=xlCalculationManual

For i = 1 To ActiveWorkbook.Names.Count

nazwa = Names(i).Name

For Each ws In ActiveWorkbook.Worksheets
ws.Activate

adres = ActiveCell.SpecialCells(xlCellTypeLastCell).Address

For Each cell In Range("A1:" & adres)
If cell.HasFormula Then
tekst_formuly = cell.Formula
If InStr(1, tekst_formuly, nazwa, vbTextCompare) Then
lista = lista & vbNewLine & nazwa & vbTab & ws.Name & "!" & cell.Address
End If
End If
Next cell

Next ws

Next i

MsgBox lista

End Sub


HIH

 

[Niek Otten]

Check this:

http://www.jkp-ads.com/RefTreeAnalyser.asp

 

[Shane Devenshire]

Hi,

Select all the sheet tabs and press Ctrl+F enter #NAME? in the Find what
dialog box set Look in to Values, and then choose Find All. When you do this
Excel will find all the cells which contain range names which were deleted
because formulas using them will return the above error.

If you want to do this for one name at a time enter the name you want to
find like "Data" into the Find what box and set Look in to Formula...

 

[Jarek Kujawa]

On second thought I would not recommend this solution
names might sometimes mess up with worksheet names as worksheets may be
named same as names and vice versa

thus this macro might lead to weird results

sorry

 

[Michael.Tarnowski]

Hi community,
is it possible - and if, how - to get a kind a map i.e. reference,
where defined names are used in a workbook?
Cheers Michael

Hi community,
I found in the net the add-in NameEdit by Jurgen Volkerink (available
at http://members.chello.nl/jvolk/keepitcool/download.html). This
handy tool allows changing of names afterwards and displays for a
given name all descendents, i.e. cells and formulas using the name.
Michael