# 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

Excel has the Row function which 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.

Does that help? I have no experience with the API so I don't know if a) Google Spreadsheets has a similar function or b) if you can call it directly.

-
That sure looks like it'll work - will check it early next week. thx – justSteve Jun 3 '11 at 17:01

`=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

Use:

``````=R[0]C[-1]
``````

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

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