Counting weekly data across two separate sheets

[tomlancaster]

Hi – I am developing a resource planner for our teams but there’s one feature I am struggling to get my head round and hope someone here can help me?

I’ve attached a sample copy of the workbook in development.

The Main Sheet has a Table which counts based on set criteria. E.g how many people are on Holiday (H)

I am trying to dynamically break the data down into 3 categories:
Daily, Weekly, Monthly

I should be able to get the data for Daily and Monthly but my problem is with Weekly.

So far I can work out the dates for Week Commencing Monday-Friday but my issue comes into play when the week runs into the following Month. E.g. Week range: 29/10/12-02/11/12 as the data will be spread across two sheets.

Can anyone help with the formula for counting between the two sheets?

Thanks for any help in advance!