Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

migrated from superuser.com Jun 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
up vote 6 down vote accepted

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.

share|improve this answer
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:

share|improve this answer
Parameter separation would be through ';' not through ','. – Jpsy Feb 12 at 12:45



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

this works in google docs, not sure about excel.

share|improve this answer
R[1]C[-1] does not = A1. Were you intentionally referencing a different cell with the usage of brackets? – OnenOnlyWalter Jan 9 '13 at 1:27
typing =R[0]C[-1] in cell B1 is the same as typing =A1. In Google docs it will auto convert one to the other. – eddy Jan 15 '13 at 3:11

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

share|improve this answer
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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.