E
EDSTAFF
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
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