Google-sheets – Range notation and current row

formulasgoogle sheetsgoogle-sheets-arrayformula

I wonder how does work the following and is it documented somewhere?

I have the sheet:

A         B
-----------------
1
2
3         =$A:$A
4
5         =$A:$A

For column B (row 3 and 5) I see corresponding values from column A. It looks like =$A:$A (or even =$A$2:$A$5 etc) works like =$A$3 and =$A$5 in some automagical manner (and no needs to specify different formulas for every cell in B).

I like such behaviour but wish to be sure that it is a reliable solution.

Best Answer

  • standalone =$A:$A means nothing, but if you use it in an array like =ARRAYFORMULA(=$A:$A) and you have some input in A column then it will return you everything you have in A column

    1

  • if you want to get cell notation of current row type in =ROW() and for full notation try:

    • =ADDRESS(ROW(), COLUMN(), 4)
    • =ADDRESS(ROW(), COLUMN(), 3)
    • =ADDRESS(ROW(), COLUMN(), 2)

    • =ADDRESS(ROW(), COLUMN(), 1)

      4

Related Topic