Changing Cell References

[Katrina]

I have a situation where I want 3 cell formulas to change
in a predictable way when I change another cell formula.
I want to be able to reference a cell offset from a
formula (so the regular offset function won't work)

For instance

If the first cell formula is a specific cell (= C2)
Then the second cell formula will be one row below that
(=C3)
the third cell formula will be in the same row as the
first cell, but in column A (=A2)
and the fourth cell will be in the same row as the second
cell but in column A (=A3)

What I want to be able to do is to change the reference
in the first cell, and have the other references move so
that the conditions above change.

So if I change
Cell 1 to = D5
the formulas for the other three automatically change to
Cell 2 = D6
Cell 3 = A5
Cell 4 = A6

Any ideas?
Thanks,
Katrina

 

[Frank abel]

Hi
if you put the text of this cell reference in a different
cell (lets say cell F1 you put D6 - without the equation
sign) use the following formula:
cell 1: =INDIRECT(F1)
cell 2: =OFFSET(INDIRECT(F1),1,0)
cell 3: =OFFSET($A$1,ROW(INDIRECT(F1))-1,0)
cell 4: =OFFSET($A$1,ROW(INDIRECT(F1)),0)