62

In Google Sheets I need to reference a cell by (column, row) in a way where I can replace "row" with a formula to find a specific row. In other words I can't use a reference like "A7" because my row is determined by a formula.

Here is the formula that gives me my row number: 

=ArrayFormula(MAX(FILTER(ROW(B:B);NOT(ISBLANK(B:B)))))
Community
  • 1
  • 1
user3143232
  • 621
  • 1
  • 5
  • 3
  • Here is a simpler function, "=COUNTA(B:B)" the result of which will be a number which represents a row. I have predetermined that I will need to reference/return the value of the cell in Column "O" which corrasponds to that row number. "=O,COUNTA(B:B)" how do I do this? – user3143232 Dec 29 '13 at 07:19
  • Take a look at the 'INDEX' function[ \[ref\]](https://support.google.com/drive/answer/3098242) – Chris Dec 29 '13 at 16:51

1 Answers1

107

You can use indirect() to dynamically reference cells

Use: indirect("string that evaluates to a reference")

example: =Indirect("A"&B2) copies the value of the cell with the row in B2 in column A

In your case, it would be (replace [column] with the desired column):

indirect("[column]"&ArrayFormula(MAX(FILTER(ROW(B:B);NOT(ISBLANK(B:B))))))
Ivo
  • 1,201
  • 1
  • 8
  • 8
  • 8
    +1 I used the INDIRECT function with the ADDRESS function to convert column headers in one sheet to row headers in another sheet by placing this function into the rows of the second sheet: `=INDIRECT(ADDRESS(1, 6 + Row(A1), , ,"Sheet 1"))` [ADDRESS function](https://support.google.com/docs/answer/3093308). Thx for the response! – mjblay Aug 15 '17 at 18:29