So, I'm working on a project in Google Sheets that has me creating a symmetric matrix. I've filled one half of it with the correct values, but I'm not happy with the idea that I have to reinput the same data (or cell references) by hand on the other half.
Is there either an option that will do this for me, or a way to otherwise automate the process? I know about paste transpose, but that gives me the same chart, with the empty half below rather than above, and still no way to get the data into the other half. I suppose I'd also accept a way to combine the two sheets, putting the filled values from one into the empty values from the other, or some sort of "transparent" paste where an empty cell in the pasted range doesn't overwrite the data in the cell it's pasted over.
Screenshot of what is filled in so far:
Best Answer
The following formula refers to the transpose of the current cell:
Indeed, it takes the row and column numbers of the current cell and swaps them to get a cell reference in R1C1 format.
However, having to paste this in every row of upper-half of the matrix separately is not appealing. (One can't select a triangular region to put the formula in). So here is a better way:
Suppose your current matrix is in the sheet named "matrix". In cell A1 of another sheet, enter
and extend this formula to a square region the size of the matrix. You get a symmetric matrix in that sheet: the formula takes either the matching cell in sheet "matrix" or its transpose, whichever is nonempty. Then select the matrix and copy values only (Ctrl-Shift-V) back into the original sheet.
Alternatively (if you don't want to mess with Indirect, maybe because your matrix is off-center and you'd need some adjustments to row/column numbers), use three sheets: one for lower half matrix, one for its transpose, and one more to combine them: