Excel Formatting Problem

  • Thread starter Thread starter Anna
  • Start date Start date
A

Anna

i have aproblem with a column that is using formula.
the formula is correct and works.
it is a sipmle formula =$g$4*$h$4/60
this is repeated over forty rows with each individual row
having its own consectutive formula i;e =$g5$*$h$5/60
the problem is that if i delete cells of information to
the left of G column and move details of information up
the formula row calculations row numbers move.
i.e =$g$4 if 2 rows of cells are deleted the formula
becomes =$g6$. must reamain =$g$4

Please help
 
Anna said:
i have aproblem with a column that is using formula.
the formula is correct and works.
it is a sipmle formula =$g$4*$h$4/60
this is repeated over forty rows with each individual row
having its own consectutive formula i;e =$g5$*$h$5/60
the problem is that if i delete cells of information to
the left of G column and move details of information up
the formula row calculations row numbers move.
i.e =$g$4 if 2 rows of cells are deleted the formula
becomes =$g6$. must reamain =$g$4

Please help
Click to expand...

The literal answer to your question is not what I would advise doing, but
the choice is yours, so here it is.
To prevent cell references changing, you can use the INDIRECT function and
enter a text string rather than a cell reference. So,
=INDIRECT("$G$4")*INDIRECT("$H$4")/60
will not alter at all as you manipulate your cells.

Your real problem, in my opinion, is that you are MOVING cells themselves
rather than their contents. I almost never MOVE cells themselves. To achieve
what I think you are doing, rather than moving the cells up by one row I
would copy the data and paste it a row higher. It may then be necessary to
clear the cell(s) in the bottom row - or you might be able to copy an extra,
blank, cell that overwrites the bottom cell.

The important thing for you to understand is that moving cells themselves
will alter your formulas (so that the formulas refer to where the cells have
moved), whereas 'moving' just the data within the cells will not.
 
Back
Top