Monthly Invoicing Excel : Partially built-need further help.

Hi All,

Attached is a Monthly Invoicing Worksheet.xlsx which I am trying to build.

Below are the rules and logic based on which I want it to work. I have already been able to cover points 1 to 4 by using vlookups and index-match function that I am familiar with. But need your help on points 5,6,7,8,11,12. I think point 12 will require a macro, hence posting on this forum.

I have myself tried to code a formula for the above points but had no luck. Hope someone can direct me. Thanks:

Rules
1)In the 'Monthly Invoice Input' sheet, first month will be chosen.
2)Next TID will be chosen
Once TID is chosen, it should populate everything else in col's C to I against that TID from "Employee Records" tab
3) Col K, 'Rate' should be calculated as per rates in "Rate Card" tab. Rate is a function of Role and Type.
4) The rules for normalised days is that one resource can only work for a maximum of 228 days in a year(Financial year Apr-Mar) - or average 19 days a month
Also, if actual days worked is 19 or below the accrued days(actual -normalised) cannot be utilised for averaging.
5)Therefore, in the 'Normalised Days' col, the cell against each resource should provide a validation such that the dropdown should contain maximum allowable days to maintain
an average of 19 days and also days below that number. The actual days worked are in tab 'Days Worked In the Year'.
Say for Example, in April I worked for actual 21 days. So my normalised days for Apr will be 19, and I will have an accrual in my name of 2 days(21-19) which can be used later to maintain the average.
Now, say I avail a vacation in May and my actual Days worked for May is 17. Then I am allowed to bill only 17 days. I cannot use the earlier 2 accruals for averaging since I have worked less than 19 days.
At this stage the 2 month average is 18(19,17). Now if I work for more than 19 days in June, say 21 days, then I am allowed to use this for averaging as per rule in 5).
So in June I will have normalised days as 21, so average will be maintained at 19(average of 19,17,21).
6)There are some people who work Partly on a Fixed Price(FP) model of billing and some others on Time and Material(T&M) -see tab Days Worked In the Year. While averaging their total days(FP+T&M) should be considered. Also, for months where there are 0 days worked, that month should not e considered for averaging.
7) I want an option to supress the above averaging rule for extraordinary situations. So if in col M 'Supress Rule(Y/N?)', the flag is set at 'Y', the user should be able put in free text value.
8)col D 'End Date' of 'Employee Record' Tab shows when the person was taken of a project. If end date is populated then his /her record upto that date should
be preserved, but his/her entry in sybsequent month should not be allowed. An error msg shouls be displayed if entry is attempted.
9)Net Invoice Amount(col P)= Normalized Days * Rate
10)Total Invoice Amount = Net Invoice Amount + Over Time Pay + Other Charges
11) There may be people who work on two or more projects. Their actual days should be aggregated to arrive at the average.
12)In tab "Monthly Invoice- By Director", the combination of Director and Month should give an extract of these entries(pulling from "Monthly Invoice Input"). If "Display in New Workbook?" flag is Y, then same extract should open in new workbook.
13) The 'Summary' tab has 2 tables a)Total by Director b) Total by contract. The running total for these should keep populating automatically.
Also, if in table "Total by Director", the aggregate in "Invoiced Thus far" exceeds 'Total PO Amount', then an alert/warning should be displayed.


Thanks