Forum Discussion
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.
7 Replies
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.
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) }I was just about to message you to ask for a TypeScript version! You must have read my mind.🙃
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 FunctionThe 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.
