Is there a formula / function for displaying the "Name" of a reference cell?

If you are in an environment in which you can run VBA, it would be possible to write a UDF (user-defined function) to return the name of a selected range.

= SHOWNAME(C7:C16)

where

Function SHOWNAME(ref As Range)
Dim oName As Name

Set oName = ref.Name
SHOWNAME = oName.Name

End Function

The logic is somewhat contorted for such a short code snippet.  The call to SHOWNAME passes a range reference to the function.  Defined names refer to formulas (text strings) that may evaluate to return a range reference (or they could simply return a value or array for example).  If it is a range that is returned, that Range object will also be assigned a Name property.  

The VBA sets the object oName to hold the Name.  One of the properties of a Name object is its name as a text string.  It is that string that is returned to the worksheet by the function SHOWNAME.

There is a further twist to this somewhat contorted story, or perhaps it's just the way I tell them?  As you select the range C7:C16 as an argument for the UDF, Excel will automatically replace the direct range reference by any named reference in the formula, so the call might read

= SHOWNAME(rangeName)

In such a case

= ISREF(rangeName)

should return TRUE.