Calendars across workbooks

Okay, here's the file I've been working on.

You asked what the Ref column is for in the Combined sheet - this just adds the appropriate name to the date generated in column H (it shows as a serial number). Then the Planning chart has the days across the top and the names down the side so that can be re-generated to determine if the cell should be shaded or not.

In the Plan_Cht sheet, I've changed the formula in B4 so that Quarter 1 starts at 1st April. This means that Quarter 4 is in a different year than the one selected in A1, so A3 now shows the actual year that the dates relate to. I've also added the days of the week to the rest of row 3. I've not changed the dates to ignore weekends, but this can be done if you would like it - I show the weekends coloured green, so they stand out quite clearly. I'm not sure what you mean about a counting function for absence, but you can use this formula (say in CP6, then copied down):

=SUMPRODUCT((B6:CO6="x")*(WEEKDAY($B$4:$CO$4,2)<6))

This will only count highlighted cells which are weekdays, even if the display shows weekend absences. I've put this in for you.

Hope this helps.

Pete