Assistance with an allocation formula

sample3.xlsxHello,

I have been stuck on a formula for a while and would be very appreciateive for any help that you may be able to provide.

I have a spreadsheet that is attached... Which is used to allocate leave to employees.

The way that it works is that each employee places a bid consisting of 4 preferences. The calculation column is used to calculate whether the employee is entitled to leave based on the number of slots that is available. If the employee's number 1 bid is successful (represented by a 1) the rest of the bids for preferences (2,3,4( fail (represented by a 0). However if bid 1 fails due to not having enough slots available, bid 2 then has the opportunity to be changed to 1.

Any help that could be preovide would be very much apreciated, and i hope that my explanation makes sense!


Kind regards,


Andrew

Hi Sixth Sense,

Rows 7 - 27 in Row G should be 0, due to the employee getting their allocation in rows 2-6 Column G. However if they missed out their allocation in rows 2-6 Column G, rows 7-27 could potentially be be successful.

I know its confusing, but ignore the data in Cells A-F.

All I need is if an employee (based on employee number) has already been successful in getting preference 1, then all their other bids are irrelevant. Or if they miss out on preference 1, then their other bid could be successful.