sorting data, formula contance

[jeff]

When I sort a group of cells by highest number to lowest one column changes
the formula to match where the cell moves to. So if cell J9 moves to cell J8
in the sorting, the formula in the j column adjusts accordingly to match the
new position by one. I want the formula to remain constant. How do i do
that? Basically, if the formula in J9 is =E9/G30 and J9 moves to J8 I want
it to stay =E8/G30, but right now it keeps changing to =E8/G29. Catch my
drift?

 

[David McRitchie]

Hi Jeff,
It's a good thing you included formula, because the words don't match
what you supply as formulas. But that is why it is important to include
the cell address and the formulas.

J9: =J8/G$30 the dollar sign keeps row 30 from changing except for
inserting/deleting of rows.

You might find the following pages of interest for other questions related to sorting
http://www.mvps.org/dmcritchie/excel/sorting.htm
http://www.mvps.org/dmcritchie/excel/offset.htm
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

 

[David McRitchie]

sorry I was trying to change it back to how the original formula would look
and inadvertently supplied a formula with a circular reference, your formula
should look like this, I think.

J9: =E9/G$30 the dollar sign keeps row 30 from changing except for
inserting/deleting of rows.

Hi Jeff,
It's a good thing you included formula, because the words don't match
what you supply as formulas. But that is why it is important to include
the cell address and the formulas.

(##see corrected formula##) the dollar sign keeps row 30 from changing except for
inserting/deleting of rows.

You might find the following pages of interest for other questions related to sorting
http://www.mvps.org/dmcritchie/excel/sorting.htm
http://www.mvps.org/dmcritchie/excel/offset.htm
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

When I sort a group of cells by highest number to lowest one column changes
the formula to match where the cell moves to. So if cell J9 moves to cell J8
in the sorting, the formula in the j column adjusts accordingly to match the
new position by one. I want the formula to remain constant. How do i do
that? Basically, if the formula in J9 is =E9/G30 and J9 moves to J8 I want
it to stay =E8/G30, but right now it keeps changing to =E8/G29. Catch my
drift?