stock allocation calculator help

Hi all,

trying to build a little calculator in excel to help with my work in the container shipping industry

what id like to do is to nominate a particular bay (container storage area on a ship) which has a given amount of space (lets say 150 TEU) for container stock which is going to a particular POD (port of discharge). there are three container types/sizes 20 foot / 20', 40' and 40'HC. the formula would allocate container stock to that bay based on the conditions i have set out. its easier explained with reference to the spread sheet attached.
the cells in red are data i would enter manually. b4:B6 is the 20', 40' and HC stock for the POD Sydney. for example i choose to allocate bay 1 to POD sydney by typing in SYD in cell g14.

the conditions for allocated of stock are as follows -
1st priority is that 20' stock is allocated. there is 150 TEUs of space in a bay for 20' stock although i want only 128 TEUs of 20' nominated to that bay. to fill the remaining space in that bay id like 11x40'HCs allocated to that bay for a total of 150 teu
once there is insufficient 20' stock to fill a bay up to the level of 128 then i'd like 40' to be allocated up to the level of 128 with 11x40'HCs to complete the balance. if no 20' and 40' to fill a bay up to 128 then to use 40'HCs

in cells d14, e14, f14 id like formulas to calculate the quantity of 20', 40' and HC stock allocated to that bay and the remaining space available in that bay in cell c14. also formulas to calculate the balance of sydney stock in cells c4:c6

i would continue to nominate bays to SYD until there is no remaining stock.

ive been trying to work this out for myself but have been unsuccessful

another feature id like to work in would be to edit the conditions without editing every formula on the page. for example say i wanted to fill a bay with 40' stock first instead of prior mentioned allocation method. ideally in column H14 > i would enter 1 which would be a reference to a particular condition/allocation

any help would be greatly appreciated!!

thanks

Reggie

A prompt and most impressive solution Jacc. thanks again