Search code examples
google-sheetsnamed-ranges

Google Sheets - Return Cell Name

I have named a cell and I often forget which cell is the main one so I am trying to put a label beside it. I would like to return the cell name in an adjacent column.

i.e.

Cell A1 has a value of 2, and has been named "Box"

In cell B1 i would like a function =Whatiscellname(A1) and the answer would "box"

I suspect this could get tricky if the cell is in multiple named ranges but to keep it simple we can pretend that we are only looking for the named range of the single cell. i.e. we can ignore the named range boxes that is cells (A1:A5)

Thanks heaps team.

I have tried a few googles.

Found this script which seems far more advanced as it is checking a value if its in a range. But I will be honest and say the logic in this one wend over my head.

Pattern 2 from this one Return the name of a named range based on cell?

Solution

  • You want a formula to show the name of the NamedRange in a given cell.

    Google Sheets does not have a native formula that will do this but it can be done with a custom function.

    Enter the formula as whatIsCellName("A1")

    • where cell A1 is cell for which you want to know the NamedRange
    • note: the cell address must be entered as a string
    /**
 * Displays The NamedRange name of the referenced cell
 *
 * @param {Array<Array<number>>} input The range to match
 * @return The NamedRange name
 * @customfunction
 */
function whatIsCellName(input) {
 
  if (typeof input !== "string"){
    throw new Error( "Cell reference must be enclosed in quotes." );
  }
  var sheet = SpreadsheetApp.getActiveSheet()
  var range = sheet.getRange(input)
  Logger.log("DEBUG: range = "+range.getA1Notation())
  var nranges = sheet.getNamedRanges()
  
  for (var i=0;i<nranges.length;i++){
    if (input != nranges[i].getRange().getA1Notation()){
      // no match, do nothing
      Logger.log("DEBUG: No match for "+input)
    }
    else{
      Logger.log("DEBUG: Matched "+input)
      return nranges[i].getName()
      break
    }
  }
}

    SAMPLE

    snapshot

    ADDENDUM

    The OP asks whether the formula could be expressed as: =whatiscellname(address(row(B71),column(B71),4))

    The answer is yes...

    /**
 * Displays The NamedRange name of the referenced cell
 * 
 * 
 * @return The NamedRange name
 * @customfunction
 */
function whatIsCellName02(input) {
  var sheet = SpreadsheetApp.getActiveSheet()
  var nranges = sheet.getNamedRanges()
  for (var i=0;i<nranges.length;i++){
    if (input != nranges[i].getRange().getA1Notation()){
      // no match, do nothing
      Logger.log("DEBUG: No match for "+input)
    }
    else{
      Logger.log("DEBUG: Matched "+input)
      return nranges[i].getName()
      break
    }
  }
}