Formula generating problem

Hi,

I have excel file with two sheets. In sheet one there is table for each employee that marks for each week his used days off(it says Off in cell),holidays(Holiday),extra days(Extra) and used extra days(UED). Each person gets two days off in each week. If he doesn't use one or two days off he gets extra one or two days and he can use them any time this year.

I am trying to have cell under each employee that will tell how many holidays has he remaining(extra day can be considered as holiday). Calculation must take into consideration default number of holidays=25, used holidays, extra days and used extra days. So if employee gets extra day Holiday remaining cell should increase by one and when he uses extra day it should decrease by one.

In sheet2 I have cells for each employee that should count used days off(used days column),holidays,extra days(Extra column) and used extra days(at the bottom of the sheet), more than 2000 cells in total. Each cell should contain formula. I have made only for employee Nenad formulas for 2nd and 3rd week. If seven days of holidays are used in one week formula should count that week as 2 used days off,7 used holidays,0 extra and 0 UED.
In cell B5 I have formula =IF(C5=7,2,COUNTIF(Sheet1!D9:D15,"Off")) and in B6=IF(C6=7,2,COUNTIF(Sheet1!D16:D22,"Off")). I need to fill in remaining cells in that row(and every other row). How can I do that?

I cant select B5 and B6 and drag them cause of relative addresses. If I do that formula in B7 would not become=IF(C6=7,2,COUNTIF(Sheet1!D23:D29,"Off")). I need some macro that would generate correct address in cell below(it would increase its row position by 7) and B8 should be =IF(C6=7,2,COUNTIF(Sheet1!D30:D38,"Off")).

Does anyone knows if this will be easy to transfer to google docs. I believe they still dont support macros but if some would give the macro that I need I could just copy cells to goggle docs. Also if anyone has a better idea or smoother way how to achieve this I would appreciate it very much.

I tried with one macro, but I deleted it so there is no macro in this file.

Excel file attached.

Tnx.