Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect worksheet and still allow user to sort data | Excel Worksheet Functions | |||
protect a sort range | Excel Discussion (Misc queries) | |||
Sort Protect Sheet | Excel Discussion (Misc queries) | |||
How to protect data in rows from being seperated during sort | Excel Discussion (Misc queries) | |||
How to password protect a cell but still be able to sort ? | Excel Worksheet Functions |