This was a useful thread. Finally figured the following to getting relative references that I think is similar to what the original poster asked.

=OFFSET(INDIRECT(TEXTAFTER(FORMULATEXT(A1),"=")),0,1)

Would give value one column to the right of the cell that A1 references in another sheet (or file as well).