I have a spreadsheet in which a particular employee’s work hours are entered in cells C4:C40 next to the particular job they are working on; and the particular job is selected in drop down boxes in each of cells B4:B40. These hours are summed against each job, in column F (F4:F13), by means of a SUMIF formula. That all works OK.
I want to then put these hours (those in F4:F13) into a table (L4:N23), such that when the employee’s total hours during the week (shown as a summation in cell C41) are less than 40, each of the hours against each of the jobs is copied forward into cells N4, N6, N8, N10, N12, N14, N16, N18, N20 and N22 (basically this means ‘normal hours’); but, when the value in cell C41 is greater than 40, any ADDITIONAL hours (but not the hours already in the even-numbered N-cells) against the various jobs is put into cells N5, N7, N9, N11, N13, N15, N17, N19, N21 and N23 (these are then ‘overtime hours’). But all I can do at the moment, when C41>40, is to make everything, everything, go into the odd-numbered N-cells. I want, effectively, for the values that end up in the even-numbered N-cells when C41<=40 to remain there, frozen, whilst all the additional hours go into the odd-numbered cells.
How can I do this with a formula? Or does it absolutely mean using VBA?
I attach the spreadsheet.