Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Map of used names?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Map of used names?
hi,
Select a cell where you want the list then Insert|Names|Paste and paste list Mike "Michael.Tarnowski" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Map of used names?
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 Mike H wrote: hi, Select a cell where you want the list then Insert|Names|Paste and paste list Mike "Michael.Tarnowski" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Map of used names?
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).Addres s 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 Uzytkownik "Michael.Tarnowski" napisal w wiadomosci ... 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 Mike H wrote: hi, Select a cell where you want the list then Insert|Names|Paste and paste list Mike "Michael.Tarnowski" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Map of used names?
Check this:
http://www.jkp-ads.com/RefTreeAnalyser.asp -- Kind regards, Niek Otten Microsoft MVP - Excel "Michael.Tarnowski" wrote in message ... 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 Mike H wrote: hi, Select a cell where you want the list then Insert|Names|Paste and paste list Mike "Michael.Tarnowski" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Map of used names?
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... -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Michael.Tarnowski" wrote: 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 Mike H wrote: hi, Select a cell where you want the list then Insert|Names|Paste and paste list Mike "Michael.Tarnowski" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Map of used names?
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 Użytkownik "Jarek Kujawa" napisał w wiadomo¶ci ... 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).Addres s 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 Uzytkownik "Michael.Tarnowski" napisal w wiadomosci ... 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 Mike H wrote: hi, Select a cell where you want the list then Insert|Names|Paste and paste list Mike "Michael.Tarnowski" wrote: 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Map of used names?
On Mar 10, 5:25 pm, "Michael.Tarnowski" wrote:
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/keepi...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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MAKE A LIST OF NAMES FROM REPEATED NAMES IN THE SAME WORKSHEET | Excel Discussion (Misc queries) | |||
COPYING FORMULA CONTAINING NAMES/RELATIVE RANGE NAMES | Excel Discussion (Misc queries) | |||
Sorting and matching rows of names with Socials with master list and eliminating the extra names | Excel Worksheet Functions | |||
Excel Spreadsheet from Access. List of names changes as names are | Excel Discussion (Misc queries) | |||
sorting names alphabetically when names may start with numbers | Excel Discussion (Misc queries) |