20

Which is the correct formula to get the value of cell on the left of current one. I've tried =OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1) but it populates the error of circular dependency.

2 Answers 2

34

I tried this and it's working (in excel at least):

=OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(ROW(),COLUMN()),"$","")),0,-1)

The issue I believe is that ADDRESS(ROW(), COLUMN()) returns something like $A$2 which locks the cell, so you need to remove the $ in the before using the offset.

EDIT: Overlooked something really simple xD (this one works both in excel and in g-spreadsheet

=INDIRECT(ADDRESS(ROW(),COLUMN()-1))
4
  • Thanks for your help, I will try if this works :) p/s: this still doesn't work I have " error: Circular dependency detected " May 27, 2013 at 8:13
  • 1
    @Vdt Hmm, I just tried a simple =offset(indirect("C8"),0,1) in g-spreadsheets and it still throws the error, whereas =offset(C8,0,1) doesn't. So, I played around a bit more and came up with a neater solution. See the edit in my answer!
    – Jerry
    May 27, 2013 at 14:42
  • 3
    Your second sample of code works great for me in Google Sheets, thanks! Jan 6, 2015 at 8:07
  • @ShaneReustle Awesome :)
    – Jerry
    Jan 6, 2015 at 8:26
5

You can use =INDIRECT(ADDRESS(ROW(),COLUMN()-1)) as already mentioned, but you can also use the simpler

INDIRECT("RC[-1]",FALSE)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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