Macro or Not? Recalculating Individual Values

Hi All

I currently use ROUND to split a store target between my sales team based on the number of hours they work through the month.

In G35 is the store target and the individual targets are calculated & displayed in G5:G32. The formula in these cells is:

Please Login or Register  to view this content.

The problem at the moment is that the individual targets do not always equal the value of G35.

What i would like is that if the sum of G5:G32 exceeds G35 then the extra sales are taken off the correct individual(s) i.e. the person with the least hours worked would lose a sale off their target then the next lowest etc. It needs to stop when G35 = The Sum of G5:G32.

If the sum of G5:G32 is lower than G35 then the opposite of the above needs to happen, i.e. highest hours gains a targeted sale and next highest gains until again G35 matches sum of G5:G32.

I have ranked the hours worked on 'rotas' tab in column BK cells 4 to 31 (28 sales advisors max in total) if that helps?

1) Should i use ranking or just the total hours worked to calculate how to make relevant adjustments?
2) Should i use a formula or macro to make this happen?

Thanks in advance
Mark

Sample.xlsm