14

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.

22

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))
  • Thanks for your help, I will try if this works :) p/s: this still doesn't work I have " error: Circular dependency detected " – nXqd May 27 '13 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 '13 at 14:42
  • 2
    Your second sample of code works great for me in Google Sheets, thanks! – Shane Reustle Jan 6 '15 at 8:07
  • @ShaneReustle Awesome :) – Jerry Jan 6 '15 at 8:26

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.