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

Yeah, it works but why resort to VBA or scripts. Press F3 and then "Paste list". It's very flexible and works anywhere in the workbook. No need for entering a (UDF) formula pointing to a specific range or have a dedicated sheet name/range to list all named ranges. 

With Office Script names and related ranges could be shown with

function main(workbook: ExcelScript.Workbook) {

    const names = workbook.getNames()
    let items: string[][] = []
    items.push( ["Name", "Range"]  )

    names.forEach( (item) => {
        if (!item.getName().startsWith("_xl") ) {
            items.push(
                [
                    item.getName(),
                    "'" + item.getFormula()
                ] )
            }
        }
    )

    workbook.getWorksheet("List of Names")
        .getRange("D2")
        .getAbsoluteResizedRange(items.length,2)
        .setValues(items)

}

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.

The name of the selected cell is displayed in the Name Box at the left end of the Formula Bar. Click the button immediately to the right to display all cell names.

Not exactly what you ask but perhaps acceptable anyhow. Press F3 to display a window with all named ranges/cells in your sheet. Then press "Paste List". This will create a list with all names and their cell addresses. Not dynamic but it may work when you don't change the named ranges very often.