Sheets: Color a cell with multiple colors based on different conditions
Lizziedesiles
Posted messages
7
Status
Membre
-
PapyLuc51 Posted messages 4566 Registration date Status Membre Last intervention -
PapyLuc51 Posted messages 4566 Registration date Status Membre Last intervention -
Good evening everyone,
I am working on a rather ambitious project for me :D
It involves coloring the same cell in 3 different colors depending on 3 different scenarios. Then, I want to apply this conditional formatting to other cells while ensuring they adapt to their environment.
As its title indicates, it involves the schedules of all the agents in the company.
There are quite a few, and it's pretty complex to navigate when trying to find an available agent for a replacement or an emergency. So at the start of the schedule, I thought about creating a quick and condensed summary of who is on duty and who is available, and to automate it.
Column 1 corresponds to schedule 1, column 2 = schedule 2, etc...
I will take schedule 1 as an example.
- When the schedule is empty (when there is no agent code entered in place of "CODE" or cell AU5), I would like cell E12 to remain white.
- If the schedule is assigned to an agent but they are not working on the corresponding day (in this case Thursday the 1st), meaning that cells AN12 to AY12 are empty, I would like cell E12 to turn green.
- If the agent is working that day, then the total hours for their day should be greater than 0 in cell AZ12, and in that case, I would like cell E12 to turn red.
► Is there one single formula to include all of this? Or do I need to create 3 conditional formatting rules for the same cell in a specific order?
► I would like to duplicate this conditional formatting to all the cells in the "quick overview of availabilities" table and adapt them to their respective schedules and days, how can I do this without having to redo everything manually?
Thank you so much in advance for your help :)
Lizzie
I am working on a rather ambitious project for me :D
It involves coloring the same cell in 3 different colors depending on 3 different scenarios. Then, I want to apply this conditional formatting to other cells while ensuring they adapt to their environment.
As its title indicates, it involves the schedules of all the agents in the company.
There are quite a few, and it's pretty complex to navigate when trying to find an available agent for a replacement or an emergency. So at the start of the schedule, I thought about creating a quick and condensed summary of who is on duty and who is available, and to automate it.
Column 1 corresponds to schedule 1, column 2 = schedule 2, etc...
I will take schedule 1 as an example.
- When the schedule is empty (when there is no agent code entered in place of "CODE" or cell AU5), I would like cell E12 to remain white.
- If the schedule is assigned to an agent but they are not working on the corresponding day (in this case Thursday the 1st), meaning that cells AN12 to AY12 are empty, I would like cell E12 to turn green.
- If the agent is working that day, then the total hours for their day should be greater than 0 in cell AZ12, and in that case, I would like cell E12 to turn red.
► Is there one single formula to include all of this? Or do I need to create 3 conditional formatting rules for the same cell in a specific order?
► I would like to duplicate this conditional formatting to all the cells in the "quick overview of availabilities" table and adapt them to their respective schedules and days, how can I do this without having to redo everything manually?
Thank you so much in advance for your help :)
Lizzie
2 réponses
Hello,
I'm continuing our exchange here in PM
Yes, a copy with some fake names and with what you have already installed (MFC from the first box).
Duplicating these MFCs all at once is going to be very complicated given the separation of all the search areas.
Right now I don't know how to proceed on Sheets;
On Excel, it's doable by duplicating horizontally, changing the search areas in each cell of the row before duplicating the entire row downwards.
That's why the only way is to work on something concrete
Method for sending a Sheets
Best regards
I'm continuing our exchange here in PM
Yes, a copy with some fake names and with what you have already installed (MFC from the first box).
Duplicating these MFCs all at once is going to be very complicated given the separation of all the search areas.
Right now I don't know how to proceed on Sheets;
On Excel, it's doable by duplicating horizontally, changing the search areas in each cell of the row before duplicating the entire row downwards.
That's why the only way is to work on something concrete
Method for sending a Sheets
Best regards
Re:
I just found a silly thing since apparently it’s related to the total in the last column of each agent’s sheet.
For the CODE cell, is it really necessary for the word to appear? Personally, I would leave it empty since on the left it says "agent code";
which allows:
In line 12 of the quick preview,
in E12 put =if($AU$5<>"",AZ12,"v")
in F12 put =if($BK$5<>"",BP12,"v") (if I calculated the number of offset cells correctly)
The "v" in the formula is specifically to distinguish the fact that the code cell is empty from "0" which is used for the green color
and so on until AJ12, and don't forget to block the code cell with "$"
There will be a series of 0s or numbers >0 or "v" if the code is empty.
Set the font to white so that the cells appear empty.
Select the cells E12 to AJ12
open conditional formatting
for green "The value is equal to 0"
for red "The value is greater than 0"
make the text and fill the same color
Then increment the selected cells down the table.
It is unnecessary to create a conditional formatting for the empty "code" cell since it's taken into account in the above formulas.
An image of what it looks like (BK5 being empty - no coloring of column F)
Best regards
I just found a silly thing since apparently it’s related to the total in the last column of each agent’s sheet.
For the CODE cell, is it really necessary for the word to appear? Personally, I would leave it empty since on the left it says "agent code";
which allows:
In line 12 of the quick preview,
in E12 put =if($AU$5<>"",AZ12,"v")
in F12 put =if($BK$5<>"",BP12,"v") (if I calculated the number of offset cells correctly)
The "v" in the formula is specifically to distinguish the fact that the code cell is empty from "0" which is used for the green color
and so on until AJ12, and don't forget to block the code cell with "$"
There will be a series of 0s or numbers >0 or "v" if the code is empty.
Set the font to white so that the cells appear empty.
Select the cells E12 to AJ12
open conditional formatting
for green "The value is equal to 0"
for red "The value is greater than 0"
make the text and fill the same color
Then increment the selected cells down the table.
It is unnecessary to create a conditional formatting for the empty "code" cell since it's taken into account in the above formulas.
An image of what it looks like (BK5 being empty - no coloring of column F)
Best regards