google-sheetsreferencegoogle-sheets-formularange

The range reference in the formula changes in one case but doesn't in other


The 3 in A3:A or B$3:B doesn't change when we move the 3rd row to any other row, but it changes when we move any other row to 3rd row position, or when insert a new row (Insert below in 2nd row or insert above in 4th row)?
Also the reference A3 doesn't change in any case.
(By move I mean this)

Example: Google Sheet (Feel free to play with)

enter image description here

If I move the 3rd row to any other row position, the formula remains the same
For Eg: I moved 3rd row to 4th row position, I get this:

enter image description here

The formula remained the same,
But now if I instead move say any row to 3rd row,
For Eg: If I move 4th row to 3rd row position like this:

enter image description here

I get this:

enter image description here

The formula changes, Similary if I say move 2 more rows to 4th row or even 3rd position then the range from A4:A & B$4:B would become A6:A & B$6:B. If you notice A3 remains the same in all cases irrespectively.

I know I can keep the range constant by using: INDIRECT(ADDRESS(3,COLUMN(A3),4)&":"&ADDRESS(ROWS(A:A),COLUMN(A3),4)) but that's not my question is, my question is:

Why is the range not changing in first case but changing in second case what exactlty is happening? and even if it's why is the cell reference A3 not changing?


Solution

  • My thoughts is that the ranges are considered from "up to down". Between the division the upper division of cells. Between the "upper border" of row 3 and the "lower border" of the last row. I experimented with the range A3:A7, B$3:B$7, and the same happens if any row inside the range is moved below the lower border of the prior 7th row, shirinkig to row 6:

    enter image description here

    So when you're dragging or creating anything outside the limits, it's considered out of range. See it illustrated with the borders in blue:

    enter image description here

    I'm just moving the rows, I haven't re-drawn any border:

    enter image description here

    enter image description here