How to find all the names of a cell?
How to find all the names of a cell?
(OP)
I have a spreadsheet where certain cells have been given multiple names. Is there a way of easily showing all the names that refer to a given cell?
In this instance, the spreadsheet deals with relay settings, and for instance zone 1 phase mho distance reach may be named Z1_, Z1P, M1P, and perhaps others. This is to facilitate macros that can then use the setting as named for that particular relay. I can see the name that comes first alphabetically in the box at the left end of the formula bar, but is there an easy way to see all the names?
At this point I use ^G (go to) to test to see if a name relates to the cell I think it might. The Insert|Name|Define method is very cumbersome given that there are probably already 1000+ named ranges and many are mind numbingly similar (great for macros, really bad for skimming through lists), and it is sorted by range name, not by cell.
In this instance, the spreadsheet deals with relay settings, and for instance zone 1 phase mho distance reach may be named Z1_, Z1P, M1P, and perhaps others. This is to facilitate macros that can then use the setting as named for that particular relay. I can see the name that comes first alphabetically in the box at the left end of the formula bar, but is there an easy way to see all the names?
At this point I use ^G (go to) to test to see if a name relates to the cell I think it might. The Insert|Name|Define method is very cumbersome given that there are probably already 1000+ named ranges and many are mind numbingly similar (great for macros, really bad for skimming through lists), and it is sorted by range name, not by cell.
RE: How to find all the names of a cell?
Range("a1").ListNames
It puts the range name in the chosen column, and the address in the next column, so you could either sort on the address column, or read it back into VBA and do what you want with it there.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: How to find all the names of a cell?
You can get the list of names and the range that each name refers to by looping through the Names collection.
Names(index).name
Names(index).refersto
or
Names(index).RefersToRange.Address
Life gets complicated because named ranges can be more than one cell.
You can test for multi-cell ranges with
names(index).RefersToRange.Cells.Count
and you can use Intersect to test if a specific cell is within a range.
So it's possible, and left to the interested student to devise a solution.
RE: How to find all the names of a cell?
CODE
Stop
Dim ThisName As Name
For Each ThisName In Names
If Not (Intersect(ThisName.RefersToRange, mycell) Is Nothing) Then
Debug.Print ThisName.Name + "- Address:" + ThisName.RefersTo
End If
Next ThisName
End Function
=====================================
(2B)+(2B)' ?
RE: How to find all the names of a cell?
=====================================
(2B)+(2B)' ?
RE: How to find all the names of a cell?
CODE
Dim ThisName As Name
For Each ThisName In Names
If ThisName.RefersToRange.Parent Is mycell.Parent Then
If Not (Intersect(ThisName.RefersToRange, mycell) Is Nothing) Then
Debug.Print ThisName.Name + "- Address:" + ThisName.RefersTo
End If
End If
Next ThisName
End Function
=====================================
(2B)+(2B)' ?
RE: How to find all the names of a cell?
=====================================
(2B)+(2B)' ?
RE: How to find all the names of a cell?
CODE
Dim ThisName As Name, NameList(1 To 100, 1 To 2) As Variant, i As Long
i = 1
For Each ThisName In Names
If ThisName.RefersToRange.Parent Is mycell.Parent Then
If Not (Intersect(ThisName.RefersToRange, mycell) Is Nothing) Then
NameList(i, 1) = ThisName.Name
NameList(i, 2) = ThisName.RefersTo
i = i + 1
End If
End If
Next ThisName
FindNamedRanges = NameList
End Function
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: How to find all the names of a cell?
CODE
Dim ThisName As Name
Dim Answer As String
Answer = ""
If mycell.Cells.Count > 1 Then
Answer = "One Cell Only Please"
Else
For Each ThisName In Names
If ThisName.RefersToRange.Parent Is mycell.Parent Then
If Not (Intersect(ThisName.RefersToRange, mycell) Is Nothing) Then
Answer = Answer & ThisName.Name & ", "
End If
End If
Next ThisName
End If
FindMyNames = Answer
End Function
RE: How to find all the names of a cell?
CODE
Dim ThisName As Name, NameList() As String, i As Long, NumRows As Long
NumRows = Application.Caller.Rows.Count
ReDim NameList(1 To NumRows, 1 To 2)
i = 1
For Each ThisName In Names
If ThisName.RefersToRange.Parent Is mycell.Parent Then
If Not (Intersect(ThisName.RefersToRange, mycell) Is Nothing) Then
NameList(i, 1) = ThisName.Name
NameList(i, 2) = ThisName.RefersTo
i = i + 1
If i > NumRows Then Exit For
End If
End If
Next ThisName
FindNamedRanges = NameList
End Function
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: How to find all the names of a cell?
Why output to debug window?
Because it's easier. (one line of code vs 6 lines of code you used to replace it)... outputs right to where I'm developing my program.
Not better, just easier. Thanks you guys for fixing it up to provide a better output.
=====================================
(2B)+(2B)' ?
RE: How to find all the names of a cell?
RE: How to find all the names of a cell?
RE: How to find all the names of a cell?
RE: How to find all the names of a cell?
Apparently that didn't work. The check assumes that the parent of name refers to a sheet. That should usually be the case, but maybe for some reason for structure of your spreadsheet it's not? (parent is workbook... or chart...?).
If you get the error again, try to examine which names are being examined and what are the parents of those names (to figure out why the check for different sheets didn't work).
=====================================
(2B)+(2B)' ?
RE: How to find all the names of a cell?
You can do it without VBA.
In Excel 2007 follow menu commands:
Formulas/Use in Formula/Paste Names.../Paste List
In Excel 2003: Insert/Names/Paste... (somewhere there).
It will display a table of all names and their addresses. Sort by addresses to identify the names corresponding to the same address.
Yakpol
RE: How to find all the names of a cell?
RE: How to find all the names of a cell?
For Excel 2000, with the cell in question selected, select Tools/Auditing/TraceDependents.
It will show arrows to all the other cells in same sheet that reference the selected cell (regardless of whether those other cells refer to selected cell by name or by address). It will also show an arrow to a little sheet icon and if you click on that arrow you see a list of all the cell addresses in other sheets that refer to your selected cell.
Then Tools/Auditing/RemoveAllArrows to get rid of the arrows
=====================================
(2B)+(2B)' ?
RE: How to find all the names of a cell?