Google Sheets – How to Build a Cell Reference Without Knowing Row Number

google sheets

This can't be a more basic spreadsheet question but I have always been a SQL kinda guy…

How do I build a formula where a value will be pulled from another column on the current row. Can't be the conventional 'E2' because the current row might be 3.

The problem boils down to defining a formula where the ROW designator is relative. $E2 works when the formula is evoked on Row #2 but fails when evoked from Row #3. I'm creating Rows thru the API – my code can't know what row number is being inserted.

Best Answer

The ROW function returns the row number of any cell or range.

The syntax is: Row( reference ) but if the reference parameter is omitted, it assumes the cell in which the Row function was entered.

This can be used to refer to cells using indirect, for example =indirect("C" & row()) refers to column C in the current row.

Related Topic