Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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

3 Answers 3

up vote 4 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.

Good luck.

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

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

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.

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

Your Answer

 
discard

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.