Adding a key (So if someone clicks "P" in the excel, it will automatically turn that cell Purple with the letter P in it)

[sheri54371]

I have created an employee attendance tracker for my staff using this template: Employee absence schedule | Microsoft Create

The template already has 5 input keys, which I have adapted to be A=Attended, L=Leave, S=Sick, P=Part Day, and W=Weekend/Public Holiday. This means that when the employee writes A in the cell corresponding to their name and the date, it will automatically show up as a green square with A written, to signify that they attended the office that way. Similarly, if they type in S, it will automatically show up as a blue square with the letter S to signal they took sick leave.

I wish to add another key for H=Work From Home, but I am unable to figure out how to do this or what was done in the template to make the original 5 key items. I can see in the top right corner (highlighted yellow) the key item has been labelled, but I cannot figure out what formulas have been input into this tab to make it. Any advice would be appreciated!

 

[Jaafar Tribak]

Most likely, the template uses Conditionnal Formatting and probably some Data Validation as welll.

Have a look in them repectively and see if that's the case.

 

[sheri54371]

Hi Jaafar,

No conditional formatting

And couldn't see anything in the data validation tab

If you have a look at the template, you'd be able to see what I'm looking at. Any advice would be greatly appreciated!
Link to template: Employee absence schedule | Microsoft Create

 

[Jaafar Tribak]

The template actually does use Conditional Formatting.

 

[SunnyAlv]

hi @sheri54371

I have added one "Conditional Formating" which is WFH to the file for you I have created a GDrive link to download the file.

Link GDrive

 

[Jaafar Tribak]

The range the CF is applied to is =$C$9:$AG$13
So you just need to add a new rule for what you want.
Before doing that, define two new range names
=V4=KeyWorkFromHome and =W4:Y4=KeyWorkFromHomeLabel
Merge and center W4:Y4 and fill the interior of V4 with the purple color.

Download amended copy
Employee absence schedule.xlsx

I have amended the first worksheet (January) for you. You should be able to do the same for the rest of the worksheets.

 

[sheri54371]

Nevermind, figured it out! Need to input the conditional formatting into the recipient cells - thanks for the nudge in the right direction!