Move cell and maintain reference

  • Thread starter Thread starter garbon2535
  • Start date Start date
G

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?
 
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?
 
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.
 
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.
 
Back
Top