Google Spreadsheet cell reference to merged cell

Question

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?

Answer 1

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.