G
Guest
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?
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?