How do I reference the values of merged cells in formulas?

Question

Say I have the following table (with cells A1 & A2 merged together to have the common value of 500):

 | Col A | Col B | Col C  |
 |-------|-------|--------|
1| 500   | 100   | =A1+B1 |
2|       | 200   | =A2+B2 |

The Value of C1 is correctly populated to 600 while the value of C2 is populated as 200 instead of the expected 700. This is because even though the cell A2 is merged with A1 which has a value of 500, the reference to A2 returns 0.

Is there any way I reference the value of the merged cell in a formula?

For instance, is there a formula that when passed a cell reference A2 returns either the value of A1 or tells me that A2 has been merged with a top left location of A1?

Are there any workarounds which are more practical than copying and pasting (and then keeping in sync) the values of A1 and A2?

Answer 1

more practical than copying and pasting (and then keeping in sync) the values of A1 and A2?

If you want A2 to have the same value as A1, do not merge them but enter =A1 in A2. This takes care of keeping them as sync; only A1 should be updated.

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.

Google Apps Script can detect merge with isPartOfMerge method, and find the range in which the cell is merged with getMergedRanges method. I wrote a custom function that retrieves the value of a cell, when cell address is passed in as a string, e.g., =cellVal("A3").

function cellVal(cellAddress) {
  var cell = SpreadsheetApp.getActiveSheet().getRange(cellAddress);
  return (cell.isPartOfMerge() ? cell.getMergedRanges()[0].getCell(1, 1) : cell).getValue();
}

This is only meant as a demonstration; I do not recommend using custom functions for retrieving data from a cell.

Answer 2

You can copy this formula down in column C starting from C1. Note that Column A can't include un-merged blank cells within the data: ¹

=INDEX(A$1:A1, XMATCH(FALSE,
   ISBLANK(A$1:A1), 0, -1))+B1

As you copy the formula down from C1, the absolute row reference in A$1 stays the same but the relative row references in A1 and B1 will change.

     A     B      C     
  +-----+-----+------------------------------+
1 | 500 | 100 | =INDEX(A$1:A1,XMATCH(FALSE,  |
  |     |     |    ISBLANK(A$1:A1),0,-1))+B1 |
  +     +-----+------------------------------+
2 |     | 200 | =INDEX(A$1:A2,XMATCH(FALSE,  |
  |     |     |    ISBLANK(A$1:A2),0,-1))+B2 |
  +-----+-----+------------------------------+
3 | 400 | 100 | =INDEX(A$1:A2,XMATCH(FALSE,  |
  |     |     |    ISBLANK(A$1:A3),0,-1))+B3 |
  +-----+-----+------------------------------+

 

---

¹ Sheets will recognize any value in a merged range as belonging to the first cell in the range. All other cells in the range are seen as empty. This formula uses these "empty" cells as an indication of being part of a merged cell above them therefore any empty cells that are not merged will also be interpreted by the formula as being part of a merged range.

Answer 3

Here is what I did and it worked for me.

     | Col A | Col B  |
     |-------|--------|
    1| 500   | =A1*2  |
    2|       | =B1    |
    3|       | =B2    |

You can then take this group and copy it some more, as needed.

     | Col A | Col B  |
     |-------|--------|
    1| 500   | =A1*2  |
    2|       | =B1    |
    3|       | =B2    |
    4| 1000  | =A4*2  |
    5|       | =B4    |
    6|       | =B5    |

Answer 4

     | Col A | Col B | Col C |
     +-------+-------+-------+
    1|       |  100  |  600  | =A1+B1
     +  500  +-------+-------+
    2|       |  200  |  700  | =A1+B2
     +-------+-------+-------+
    3|       |       |       |
     +-------+-------+-------+

if you need to drag it then lock down the number:

     | Col A | Col B | Col C |
     +-------+-------+-------+
    1|       |  100  |  600  | =A$1+B1
     +  500  +-------+-------+
    2|       |  200  |  700  | =A$1+B2
     +-------+-------+-------+
    3|       |       |       |
     +-------+-------+-------+