Reference a cell using formula?

google-sheets reference
user3143232 picture user3143232 · Dec 29, 2013 · Viewed 83.3k times · Source

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)))))

Answer

Ivo picture Ivo · Nov 25, 2015

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))))))

Related questions

Get value from the cell above

Is it possible in Google Spreadsheets to reach the value of the cell just above? Meaning: In one cell A2 I want to display the value from the cell above, that is A1. Not because it is that coordinate, but …

Read More
How to highlight cell if value duplicate in same column for google spreadsheet?

I am looking for formula for google spreadsheet highlight cell if value duplicate in same column can anyone please assist me for this query?

Read More
Conditional formatting based on another cell's value

I'm using Google Sheets for a daily dashboard. What I need is to change the background color of cell B5 based on the value of another cell - C5. If C5 is greater than 80% then the background color is green …

Read More