Retrieving Cell name in Excel

Question

Is there a way of displaying the Cell name of a particular cell in another cell? I would like to display the cell name in the adjacent cell so that the user is able to identify the cell name without clicking it.

Thanks...

Answer 1

It doesn't seem to be possible, which is weird. You'd think that the cell() function should provide a way to get the name, but it doesn't. Bummer.

Answer 2

This will work in very basic circumstances:

Public Function CellName(cel As Range) As Variant
Dim nm As Name
    For Each nm In Names
        If nm.RefersTo = "=" & cel.Parent.Name & "!" & cel.Address Then
            CellName = nm.Name
            Exit Function
        End If
    Next
    CellName = CVErr(xlErrNA)
End Function

It won't work if the cell is part of a named range, it won't show multiple names for the cell, it won't work for cells included in named formulae (like =OFFSET() ranges, for example).

The

"=" & cel.Parent.Name & "!" & cel.Address

thing is pretty clunky, too. There may be a better way to do the check. Creating a Range object from the RefersTo and using Intersect() might work.

Answer 3

Excel does have a function "Cell()" that you can get certain properties from.

You can use =Cell("row", K9) and get back row number 9 and there's an equivalent "col" parameter but it returns the column number (11) rather than the letter.

Answer 4

This function would give the name of the NamedRange the cell belongs to:

Public Function CellName(oCell As Range) As Variant  
Dim oName As Name  
For Each oName In ThisWorkbook.Names  
    If oName.RefersToRange.Parent Is oCell.Parent Then  
        If Not Intersect(oCell, oName.RefersToRange) Is Nothing Then  
           CellName = oName.Name  
           Exit Function  
        End If  
    End If  
Next  
CellName = CVErr(xlErrNA)  
End Function

It loops through all the names in the workbook, and then for each name it checks if it refers to any thing in this the sheet input parameter is from. If it is then it checks if the input cell and the rages referred by the name intersect. If they do it returns the name of the range.