Protect formulas during sort
 

Excel 2000.
In a Pareto charting effort I am importing defect data from an Access
database using an excel template, then to the side I have a list of the
defects and a column that uses €œCOUNTIF€� to summarize the data for each
defect. The data is then sorted in descending order by the €œCount€� column
and the top ten defects are used to create a bar chart. The template is kept
on our intranet so that it can be accessed by multiple users.
The problem is that when I sort the summary column that uses the COUNTIF
function the cell references in my formulas change. If a row moves down
during the sort it loses the reference for the upper cells.

Example:
=COUNTIF(C2:F1000,"Bond Wire Lifted")
Changes after sort to;
=COUNTIF(C4:F1002,"Bond Wire Lifted")

I just lost the top two rows of data from the sort.
If I protect the cells the sort doesn't work. Is there a way to protect
only the formulas in a cell so that the results can be accurately sorted?

Protect formulas during sort
 

use the absolute reference
=COUNTIF(C$2:F$1000,"Bond Wire Lifted")

"MMMM" wrote:

Excel 2000.
In a Pareto charting effort I am importing defect data from an Access
database using an excel template, then to the side I have a list of the
defects and a column that uses €œCOUNTIF€� to summarize the data for each
defect. The data is then sorted in descending order by the €œCount€� column
and the top ten defects are used to create a bar chart. The template is kept
on our intranet so that it can be accessed by multiple users.
The problem is that when I sort the summary column that uses the COUNTIF
function the cell references in my formulas change. If a row moves down
during the sort it loses the reference for the upper cells.

Example:
=COUNTIF(C2:F1000,"Bond Wire Lifted")
Changes after sort to;
=COUNTIF(C4:F1002,"Bond Wire Lifted")

I just lost the top two rows of data from the sort.
If I protect the cells the sort doesn't work. Is there a way to protect
only the formulas in a cell so that the results can be accurately sorted?

Protect formulas during sort
 

Thank You!

"bj" wrote:

use the absolute reference
=COUNTIF(C$2:F$1000,"Bond Wire Lifted")

"MMMM" wrote:

Excel 2000.
In a Pareto charting effort I am importing defect data from an Access
database using an excel template, then to the side I have a list of the
defects and a column that uses €œCOUNTIF€� to summarize the data for each
defect. The data is then sorted in descending order by the €œCount€� column
and the top ten defects are used to create a bar chart. The template is kept
on our intranet so that it can be accessed by multiple users.
The problem is that when I sort the summary column that uses the COUNTIF
function the cell references in my formulas change. If a row moves down
during the sort it loses the reference for the upper cells.

Example:
=COUNTIF(C2:F1000,"Bond Wire Lifted")
Changes after sort to;
=COUNTIF(C4:F1002,"Bond Wire Lifted")

I just lost the top two rows of data from the sort.
If I protect the cells the sort doesn't work. Is there a way to protect
only the formulas in a cell so that the results can be accurately sorted?