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 "N...
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.