Menu
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))))) 
761
To create an Excel dynamic reference to any of the above named ranges, just enter its name in some cell, say G1, and refer to that cell from an Indirect formula =INDIRECT(G1) .
Now there are three kinds of cell references that you can use in Excel: Relative Cell References. Absolute Cell References. Mixed Cell References.
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)))))) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
