# Build a cell reference without knowing Row # (number)

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. - ## migrated from superuser.comJun 3 '11 at 14:13 This question came from our site for computer enthusiasts and power users. You might want to state what program you're using. – Daniel Beck Jun 2 '11 at 13:20 googleDocs' spreadsheet but i'll eat my hat if it's different than excel. – justSteve Jun 2 '11 at 13:52 ## 4 Answers 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. - The$ in excel (and google doc's spreadsheet) is used to create absolute (non relative) references. In other words, $e$3 will refer to e3 no matter where it is copied to, from anywhere its copied to. You must lock the column and row individually (or the other one will change).

-
Also applies to Apple Numbers. – Daniel Beck Jun 2 '11 at 13:29
[sry for the misclicks on the votes] I'm looking for the Absolute Column Relative Row expression. Always want value of E from row relative to current row. – justSteve Jun 2 '11 at 13:51
$e2 or whatever number you want there. – soandos Jun 2 '11 at 13:54 isn't '2' absolute? I need a way to specify 'current row'. – justSteve Jun 2 '11 at 16:17 The 2 is not absolute, just the e is. The$ only locks the thing that follows it immediately, not the whole thing. – soandos Jun 2 '11 at 16:22

=R[0]C[-1] would make sense but R1C1 notation isn't working in the new version of Google Sheets except within the INDIRECT(ref, FALSE) function, e.g. INDIRECT("R1C1", FALSE).

So write:

=INDIRECT("R[0]C[-1]", FALSE)
-
Parameter separation would be through ';' not through ','. – Jpsy Feb 12 at 12:45
@Jpsy Depends on your locale. It is , for some countries and ; for others. – sandwich May 11 at 0:58
Whow @sandwich – that explains constant hassles I had with the docs. AFAIK even Excel has this difference depending on the locale. Surely a big problem for many users who run into this problem without understanding why their commands do not work as described in the docs. Thanks for the explanation! – Jpsy May 20 at 6:35

Use:

=R[0]C[-1]

The square brackets mean it is a relative reference. =R1C1 is the same as =A1