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.
Add a comment
|
2 Answers
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 " 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!– JerryMay 27, 2013 at 14:42 -
3Your second sample of code works great for me in Google Sheets, thanks! Jan 6, 2015 at 8:07
-
You can use =INDIRECT(ADDRESS(ROW(),COLUMN()-1)) as already mentioned, but you can also use the simpler
INDIRECT("RC[-1]",FALSE)
