Excel - Color Coding Cells in Main Employee List Based on Leave and Sick Status Columns

Welcome back.

Before I ask a question here, I try to solve it by looking in ready-made topics. I found solutions to problems similar to mine, but without the possibility of applying them in this case. The point is that I have three columns on one sheet. One contains the names of all employees, the second is a list of employees on vacation, and the third is a list of sick employees. I would like the cells with employees on leave to change their color on the main list (all employees) to e.g. blue, and those on sick leave to e.g. orange.

I tried to do it with the simplest method for a layman, that is with conditional formatting of cells from the main list, but as usual I got stuck on a formula that would search for these employees from the "absentee" lists.

I hope someone has already rubbed against something similar and can help me with that.

Regards.

If you don't mind a color source column, the easiest way is:

A great idea - by the colors. There are only two problems: at work I have excel2003 and I'm afraid that there is not even such an option there, and secondly - I have had Excel 2007 recently and I do not know how to set the formatting in your way . I don't dare to ask for a "physical" example - you learn new things in your life ... I'm just afraid that it won't work in the 2003 version ...

In E2007, the matter is very simple. You select the columns (non-contiguous by holding down the Ctrl key), select> conditional formatting> format only unique or duplicate values. You choose the filling and that's it.

I saved in E2k3 format. Formatting partially works in OpenOffice (I don't have E2003). The formula, hmm, after converting to OO was a bit long but quite understandable. It should be straightened and a second one added. Maybe someone will check in E2003, while throwing prtScr with the rules window?



In the attachment the above-mentioned file.

Not so much that I improved but - I added a new line to change the color of both options (holidays and sickness). It works fine, but significantly slows down the operations performed on this sheet by a few seconds. It seems that this form of formatting is a heavy burden on the system (budget computer at work).

https://obrazki.elektroda.pl/8781410300_1331898630.jpg