1

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.

  • 1
    I was thinking about it but not sure that it answers. =$A:$A neither returns full list of values from $A:$A nor extends values to multiple rows. It looks that Sheets automatically replaces =$A:$A with something like =INDEX($A:$A; ROW()) but it is not obvious. – crazyh Mar 24 '18 at 1:00
0
  • 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

  • > standalone =$A:$A means nothing I was thinking so but see docs.google.com/spreadsheets/d/…. It looks like =$A:$A works like some kind of array getter "=$A:$A[ROW()]". And I still did not find where such behaviour documented. – crazyh Jan 18 at 23:17
  • But I do not use ARRAYFORMULA but ="A:A is " & A:A expands to 'A:A is 2', 'A:A is 5' etc. And it is not clear how to call such type of reference. – crazyh Jan 20 at 1:47
  • How it can be "nothing" when in test sheet above it is alone and is anything? And Sheets somehow knows that only single row from A:A matrix should be displayed. – crazyh Jan 20 at 2:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.