Sort Columns in Protected Worksheet, etc.
LONG MESSAGE, SORRY
I have created a workbook with 10 Sheets. Sheet 1 is "Roster" Sheet 2 is "All Certifications" Sheets 3-10 are various individual certifications. Sheets 2-10 C6:C65 are linked to Sheet 1 C6:C65 (Staff Names) Sheet 2 D6:M6 is linked to end result of Sheets 3-10 (Days until renewal, color-coded cells via conditional formatting). ON SHEET 1: C4="Name"; D4="Home Number"; E4="Mobile Number" F4=Pager; G4=Email; etc. A6:A65= 1-60 Column B is empty and hidden (sorting seems to occur better if blank adjacent column). C6:C65 is names of staff members. All cells are locked except C6:J65 (though when project is completed all cells except empty cells will be locked). Cells are grouped via column such that C6:J6 is recognized as one group so that if C6 is sorted/moved, D6:J6 moves as well. Grouping continues through all unlocked cells (C7:J7 THRU C65:J65). PROBLEM 1: The problem I am having is if I lock the worksheet, sorting capability is stopped. How can I lock the worksheet to prevent loss/altering of data yet maintain sorting capability? It is important that this Roster sheet be protected as Sheets 2-10 C6:C65 are linked to Sheet 1 C6:C65. For instance, if I sort data via name in Sheet 2, the sorting occurs based on sorting in Sheet 1. PROBLEM 2: In Sheets 3-10, Column E =Renewal Date; Column F (Hidden) uses date function "=Today()". Column G uses "-(F6-E6) to determine proximity to renewal date. In addition, I used Conditonal Formatting so that 180=Green, 0-180=Yellow, and <0=Red. The problem I have is if a staff member is removed from the Roster on Sheet 1 (linked to C6:C65 of sheets 2-10), the cell color changed by above formula remains changed. Is there a hidden function / formula I can add to reverse the color changes made by above conditional formatting such that if cell reference to Sheet 1, C6:C65 is removed, color changes in linked sheets are also removed? Problem 3: Similar to Problem 2, On Sheet 1 (Roster), Column H= Birthdate (day/Month/NO YEAR) format; Column I (Hidden)=Today's Date() such that J6=-(I6-H6) (days until employee's birthdate). Conditional Fomatting as described in Problem 2 is used such that the cell color is changed based on proximity to staff member's BD. As in Problem 2 above, once the color is changed via formulas of conditonal formatting, the cell color remains changed even if staff member's name is removed from roster. Is there a way I can change the formula to revert back to pre-conditional formatting if name is removed? TIA |
All times are GMT +1. The time now is 02:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com