How do I get Google Sheet Cell References to Change When I Move Rows?

Question

I am use Google Sheets to create set lists for my band.

Each song has columns for "start time," "song length," and "set progress" so we can see how we're doing time-wise during our shows.

My problem: When I drag songs rows up or down to reorder the set list, cell references do not change and I have to copy and paste cell references to get the "start time" and "set progress" to refelect the proper timing.

How do I get the cell references to update when I change the row order? Here's my sample sheet

Answer 1

You want the formulas to always refer to preceding row, even if the rows are rearranged. This can be done with offset. For example, the cell directly above D3 is offset(D3, -1, 0); the cell to the left of D3 is offset(D3, 0, -1). In general it's (cell, number of rows, number of columns).

In D3, enter =offset(D3, -1, 0) + offset(D3, 0, -1) (you can also use SUM() here, though it seems unnecessary) and copy down the column. Importantly, the only named reference here is to the cell itself, everything else is relative to that. So if the rows are interchanged, the structure "cell above", "cell to the left" remains.

Similarly, in B3 one can use =offset(B3, -1, 0) + offset(B3, -1, 1) to refer to the cells directly above and above-and-to-the-right. Or, in a shorter form, =sum(offset(B3, -1, 0, 1, 2)) where the last two parameters are the row/column size of the range being summed.