24

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.

Improve this question
2
  • You might want to state what program you're using.
    – Daniel Beck
    Commented Jun 2, 2011 at 13:20
  • googleDocs' spreadsheet but i'll eat my hat if it's different than excel.
    – justSteve
    Commented Jun 2, 2011 at 13:52

5 Answers 5

Reset to default
26

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.

Improve this answer
0
7

=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)
Improve this answer
3
  • Parameter separation would be through ';' not through ','.
    – Jpsy
    Commented Feb 12, 2016 at 12:45
  • 2
    @Jpsy Depends on your locale. It is , for some countries and ; for others.
    – user79865
    Commented May 11, 2016 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
    Commented May 20, 2016 at 6:35
4

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

Improve this answer
8
  • Also applies to Apple Numbers.
    – Daniel Beck
    Commented Jun 2, 2011 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
    Commented Jun 2, 2011 at 13:51
  • $e2 or whatever number you want there.
    – soandos
    Commented Jun 2, 2011 at 13:54
  • isn't '2' absolute? I need a way to specify 'current row'.
    – justSteve
    Commented Jun 2, 2011 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
    Commented Jun 2, 2011 at 16:22
1

This actually works and doesn't change the offset when inserting or removing rows or columns. The reverence here gets the value from 3 rows up and 5 cells left:

=INDIRECT(CONCATENATE("R", ROW() - 3, "C", COLUMN() - 5), FALSE)

Change the values after ROW() and COLUMN to +/- the offsets you desire.

I needed a lookup to a separate sheet based on the value in same row and one column to the left. My problem was that I wanted to cut and paste text for the formula anywhere in any sheet, row, or column and have it work the same.

This is the LOOKUP formula I wanted and am using currently with success:

=IF(INDIRECT(CONCATENATE("R", ROW(), "C", COLUMN() - 1), FALSE) = "", "", VLOOKUP(INDIRECT(CONCATENATE("R", ROW(), "C", COLUMN() - 1), FALSE), Lookups!$A$2:$B$20, 2, TRUE))
Improve this answer
-1

Use:

=R[0]C[-1]

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

this works in google docs, not sure about excel.

Improve this answer
2
  • 1
    R[1]C[-1] does not = A1. Were you intentionally referencing a different cell with the usage of brackets?
    – OnenOnlyWalter
    Commented Jan 9, 2013 at 1:27
  • 1
    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
    Commented Jan 15, 2013 at 3:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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