Forum Discussion
Solved
Conditional Formatting in a roster
Hi team.
Help please... I have a roster spreadsheet that shows a year attendance. Each day is a new column.
I have a list/legend of conditional formatting for what the employee is doing on each day. For example: D = day shift. SL = sick leave. T = training etc.
The list of conditional formatting is in a legend below the roster where we have managed the rules and have the different codes and colours etc.
How do I apply this list to the whole roster please?
I suggest you start from scratch. In version 3, delete the formulae at the bottom of the data table (rows 10 to 42). These cause Excel to see 33 records with a blank date. Hence, it adds a blank year (the check box without a year number) to the filter window.
Now convert your data table (Ctrl-T) into a so-called structured table. If you now enter a new date in column E, the table will automatically expand and copy down the formulae from the previous row.
Then, change the data source for the PT to this table, called "Table2" in the attached example. It will also avoid blanks. Referring column A:H is okay as well, but then you'll end up with the blanks that need to be filtered out.
2 Replies
Hi Christine_Jones,
to apply a list of conditional formatting rules to a whole roster, you can use VLOOKUP:
- Select the entire roster, including the header row.
- Click on Home > Conditional Formatting > New Rule....
- In the New Formatting Rule dialog box, select the Use a formula to determine which cells to format option.
- In the Format values where this formula is true box, enter the following formula:
=VLOOKUP(A2,Legend!$A$2:$B$10,2,FALSE)Where A2 is the cell in the roster that you want to apply conditional formatting to, and Legend!$A$2:$B$10 is the range of cells in the legend that contains the list of conditional formatting rules.
- Click on the Format button and select the desired formatting for the cells that meet the condition.
- Click on OK to close the Format Cells dialog box.
- Click on OK again to close the New Formatting Rule dialog box.
This will apply the conditional formatting rules from the legend to the entire roster.
Here is an example of a conditional formatting rule for a roster:
=VLOOKUP(A2,Legend!$A$2:$B$10,2,FALSE)Where A2 is the cell in the roster that you want to apply conditional formatting to, and Legend!$A$2:$B$10 is the range of cells in the legend that contains the list of conditional formatting rules.
This rule will look up the value in cell A2 in the first column of the legend, and then return the value in the second column of the legend. If the value in cell A2 is not found in the legend, then the rule will return a blank value.
You can create multiple conditional formatting rules for your roster, and each rule will be applied in the order that you create them.
You can also use the AND and OR logical operators to combine multiple conditions in a single conditional formatting rule. For example, the following rule will apply conditional formatting to cells that are both empty and contain the value "D":
=AND(A2="",VLOOKUP(A2,Legend!$A$2:$B$10,2,FALSE)="D")Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.If the post was useful in other ways, please consider giving it Like.
Kindest regards,
Leon Pavesic
LeonPavesic Thank you.
