Forum Discussion

Sgreever's avatar
Sgreever
Copper Contributor
Jan 17, 2025

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

I am trying to see if there is an Excel function to display the "Name" of another cell.  I looked through all of the options using the =CELL() function (there are many) but none seem to return the "Name" that I have given the cell.  I have used reference names in several places within my spreadsheet to simplify formulas and make them more readable, but I would like to have a dictionary of these referenced inputs including what each one is named in a separate tab.

Any pointers are appreciated.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    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. 

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP

      Riny_van_Eekelen , of course, you are right. Most probably F3 is enough. VBA and script are only to illustrate possibilities, especially if we'd like to expand information, e.g.

      function main(workbook: ExcelScript.Workbook) {
      
          const names = workbook.getNames()
          let items: string[][] = []
          items.push( ["Name", "Range", "Scope", "Type", "Comment"]  )
      
          names.forEach( (item) => {
              if (!item.getName().startsWith("_xl") ) {
                  items.push(
                      [
                          item.getName(),
                          "'" + item.getFormula(),
                          item.getScope().toString(),
                          item.getType().toString(),
                          item.getComment(),
      
                      ] )
                  }
              }
          )
      
          const tables = workbook.getTables()
          if (tables.length > 0) {
          tables.forEach( tbl => {
              items.push(
                  [
                      tbl.getName(),
                      tbl.getRange().getAddress(),
                      "Workbook",
                      "Table",
                      ""
                  ]
              )
          }  )
          }
      
          const pivotTables = workbook.getPivotTables()
          if (pivotTables.length > 0) {
              pivotTables.forEach( tbl => {
                  items.push(
                      [
                          tbl.getName(),
                          tbl.getWorksheet().getName(),
                          "Workbook",
                          "PivotTable",
                          ""
                      ]
                  )
              } )
          }
      
          workbook.getWorksheet("List of Names")
              .getRange("D2")
              .getAbsoluteResizedRange(items.length,5)
              .setValues(items)
      
      }

      All depends on goals.

  • 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.

     

     

     

     

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    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.

     

Resources