Google Spreadsheet cell reference to merged cell

Related searches

I would like to reference a merged cell and get the value of the merge cell from any of the cells that fall within the merged cell's range.

Using =CONCATENATE(A2, " ",B2) in C1 and dragging it down works for: un-merged cells, and the first cell of a merged cell. It does not work for subsequent cells in a merged group. This works as expected in MS Excel, but not Google spreadsheets, is there a way to achieve this?

I wrote the following script, which works only for merged columns (which is what I wanted):

/**
 * Returns the value at the top of a merged cell. If the merged cell is blank, not at the top of the sheet, and below another merged cell, the the function overflows into the above merged cell and returns incorrect input.
 *
 * @param {int} column Column number of the cell.
 * @param {int} row Row number of the cell.
 * @return The value shown in the merged cell.
 * @customfunction
 */
function FIRST_IN_MERGED_COLUMN(column, row) {
  var sheet = SpreadsheetApp.getActiveSheet();

  var cell = sheet.getRange(row, column);

  if (!cell.isPartOfMerge())
    return cell.getValue();
  else if (cell.getValue() != "")
    return cell.getValue();
  else {
    var r = row;
    var newCell = cell, oldCell = cell;
    while (r > 1) {
      if (!(newCell = sheet.getRange(--r, column)).isPartOfMerge())
        return oldCell.getValue();
      if (newCell.getValue() != "")
        return newCell.getValue();
      oldCell = newCell;
    }
    return ""; // Blank merged cell at top of sheet
  }
}

Unfortunately, if one merged column is directly above another merged column, and the bottom merged column is blank, then the script will use the value of the top merged column.

It is used below, column D shows the formula in column B, and success or error case are shown in column C.

Getting a formula to reference a Merged Cell , Spreadsheet formulas have to refer to merged cells by their top Google Apps Script can detect merge with isPartOfMerge method, and find� Merging cells in Google Sheets is a great way to keep your spreadsheet well-organized and easy to understand. The most common use is for headers to identify content across multiple columns, but regardless of the reason, it’s a simple process. Fire up your browser and head to the Google Sheets home page.

I found a better way:

// example inputs for cell position
var row = 5;
var column = 7;

// Get the range that the cell belongs and then get the displayed value.
var cell = sheet.getRange(row, column);

var result;

if (!cell.isPartOfMerge()) {
  result = cell.getValue();
} else {
  // get the range the cell belongs
  var range = cell.getMergedRanges()[0];

  // getting the column and the row of the top left cell in the range
  var colRef = range.getColumn();
  var rowRef = range.getRow();

  // getting the value
  result = range.getDisplayValue();
}

How do I reference the values of merged cells in formulas?, How to Merge and Unmerge Cells in Google Sheets. I don't prefer or recommend merging cells in Spreadsheets as it might affect some formulas especially� Spreadsheet formulas have to refer to merged cells by their top leftmost entry. They cannot detect that cells were merged. Thus, it is not advisable for cells with data that will be used elsewhere. Cell merge is fine for text, e.g. to have a header of a table spanning several columns.

I combined the other answers to this function:

function MERGED_CELL_VALUE(column, row) {
  var sheet = SpreadsheetApp.getActiveSheet();

  var cell = sheet.getRange(row, column);

  if (!cell.isPartOfMerge())
    return cell.getValue();
  else if (cell.getValue() != "")
    return cell.getValue();
  else {
    var range = cell.getMergedRanges()[0];
    return range.getDisplayValue();
  }
}

How to Find the Cell Addresses of the Merged Cells in Google Sheets, In a Google docs spreadsheet. If cells A1 & A2 are merged, is there a way to confirm they are merged, using google apps script? There is a merge function in GAS https://developers.google.com/apps-script/class_range#merge But there is no function or example that shows how to check if cells are merged.

This is my attempt to collate all of the best attributes of all of the other answers. I've torture-tested it in every way I can think of, and it seems to hold up pretty well. That said, error reports are welcomed.

Usage: call it with the cell reference in quotes: =MERGED_CELL_VALUE("H19"), not =MERGED_CELL_VALUE(H19). I couldn't make the function always raise an informative error if you call it without quotes, since the contents of H19 could easily be A2 or something. But it'll usually complain in a useful way.

If you want to be able to use click-and-pull autofill with this, there's a fairly easy workaround. Use CELL() as so: =MERGED_CELL_VALUE(CELL("address", H19)). Now, H19 is bare, so it'll be updated if you move it around. This trick also works with H$19 and the like.

/**
 * Returns the value at the top of a merged cell.
 *
 * Inspired by the code at:
 * https://stackoverflow.com/questions/41967357/google-spreadsheet-cell-reference-to-merged-cell
 *
 * @param {str} target The targeted cell, as a string in A1 format.
 * @return The value shown in the merged cell.
 * @customfunction
 */
function MERGED_CELL_VALUE(target) {
  var sheet = SpreadsheetApp.getActiveSheet();
  try {
    var targetRange = sheet.getRange(target);
  } catch(e) {
    if (target == "") {
      throw "Empty target specified.  Did you forget to enclose the targeted cell in quotes?"
    }
    else {
      throw "Invalid target '" + target + "'.  Did you forget to enclose the cell ID in quotes?";
    }
  }

  if (!(targetRange.getNumColumns() == targetRange.getNumRows() == 1)) {
    throw "This function does not work on ranges (e.g. 'A1:B3'), only individual cells.";
  }

  var cell = targetRange.getCell(1, 1); // n.b. Range.getCell() is 1-indexed because FML.

  if (!cell.isPartOfMerge()) {
    // If the cell is not part of a merge, return the cell's value.
    return cell.getValue();
  }
  else if (cell.getValue() != "") { // n.b. this is a special case for getValue(); empty returns "".
    // If the cell has a value, even if it's merged (!!??), return the value.
    return cell.getValue();
  }
  else {
    /*
     * This might turn out to be a bug; we pick getMergedRanges()[0].  Hopefully that matches how GSheets's logic works.
     * There is a getDisplayValue(), but that always returns a String, which is unlikely to fit our goal in
     * all cases.
     */
    return cell.getMergedRanges()[0].getCell(1, 1).getValue();
  }
}

Using cell reference in Google Sheets Query is a little tricky but easy to follow once you start using it. Use this tutorial as a reference guide whenever you want to use cell reference as a criterion/condition in the Query WHERE Clause in Docs Sheets.

To use CONCATENATE, open your Google Sheets spreadsheet and click an empty cell. You can use CONCATENATE in several ways. To link two or more cells in a basic way (similar to CONCAT), type =CONCATENATE (CellA,CellB) or =CONCATENATE (CellA&CellB), and replace CellA and CellB with your specific cell references.

Press F4 a second time to create a mixed reference where the row number is absolute, such as A$6. Press F4 a third time to create a mixed reference where the column letter is absolute, such as $A6. Press F4 a fourth time to make the cell reference relative again, such as A6.

when you merge cells A1&A2 it becomes cell A1 only. likewise if you merge cells B1:B5, the name of merged cell will be B1 only. you can fetch data from merged cells by referencing to only first row and first col of merged cells.