Selected Answer
Hi again Ghost
In the first revised file attached, you'll see column M is colour-coded but the values are not changed (i.e. no additional text).
That's done by conditional formatting of a new (narrow) column, just to the right of column M. Click there and your old data validation rule (was in M12 etc.) is used with your original Data Validation source:
=$AD$15:$AD$17
so you can pick Sick, holiday or Unknown.
There's no border between cells plus I left-justified column N and made it narrow so that a Sick entry looks like "08:30-17:00 S" (but it's two cells- you can't merge them).
Data validation in M12 (for "Sick") is based on a formula:
=IF($N12=$AD$15,1,0)
(with a blue format fill) and Applies to set to:
=$M$12:$N$25
so is Sick is selected, both cells go blue.
I then created two other rules for Holiday and Unknown ($AD$16 and $AD$17).so you can use a dropdown for all three categories.
You'd need to repeat this for your other time columns (you can't just extend the CF range.
One downside is that if there's nothing in the cell to the right of the "category" column N, the chosen category wording runs ino it (e.g. I had to put a space in cell O18 to prevent that).
REVISION 05 Feb:
As an extension of the above, you could add a VBA event macro to reveal/hide the "category" column, as in the second file attached below:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("M12:N25")) Is Nothing Then
' if outside this time/category range
Columns("N:N").Hidden = True
Else
' if time column, reveal category column
Columns("N:N").Hidden = False
End If
End Sub
Click in cell M13 say, and column N will appear. Click away i column P say, and column N will disappear. (Note- I corrected to spelling of Hoilday in cell AD16 in this file).
Here again, you'd need to duplicate that code (in the same event macro) per column of time values (/ catagories).
Hope this works for you or gives you a bettter idea.