Move cell and maintain reference

[garbon2535]

I have a worksheet with a simple function. I want to be able to move the
function from one cell to another cell and maintain a reference to a cell in
a certain location relative to the new cell.

Example:

My function is in Cell A3. The Function has a reference in cell A1 which is
two rows above Cell A3. When I move the function in cell A3 to cell A10 it
still references cell A1. I want it to maintain the relationship it had and
reference the cell two rows above the new cell which would be cell A8.

When I copy the cell from once place to another I do not have this problem.
I only experiance this problem with I move a cell. How can I make a formula
that I can move all around and it will maintain a reference with a cell two
cells above the formula's cell?

 

[RagDyer]

What is the formula you're using?

Why can't you copy the formula to your various cells and then delete it when
you're done with it?

 

[garbon2535]

My formula is simple. It adds the contents of a cell two above it to the
results of the cell to the right and above it divided by 24. An example
would be:

Cell C12 contains the formula C10+(D11/24)

I can copy the cell to a new location and then delete the old cell but I am
working with very big spread sheets with 100's of these simple formulas. It
would be much harder to make a mistake if I could cut and paste instead of
copy, paste, delete. Problems arise when a cell is copied and then the old
cell is not removed or if an adjacent cell is removed instead.

Another alternative I use is after I move a cell, I copy a "good" formula
and then paste it into the new cell from a different location. This again
causes errors when I forget to copy a "good" formula onto the bad one.

 

[garbon2535]

I have found a solution to my problem. To anyone interested, here is my new
formula.

In cell C19 I have the following:
=(OFFSET(C17,-2,0))+((OFFSET(C17,-1,1)/24))

This will add cell C15 to the results of D16/24

You can Move this cell anywhere on the spread sheet and it will retain it's
relationship to 2 cells above and one cell above/ one cell over.

 

[Glenn]

Another way:

=INDIRECT("r[-2]c",0)+INDIRECT("r[-1]c[1]",0)/24