Inventory management problem, capping values

Hi

I have an inventory management problem that I've struggled with for days now. If anyone can help me, I'll buy them the biggest beer I can find if they ever make it to Singapore!

I'm trying to cap the allocation of units sold on specific routes once all available inventory on that route is sold but it's not as simple as using a MIN formula and I'll explain why.

In the example attached on the Summary Journey sheet, I have:

- Column B shows all the possible routes we can sell units on. Routes are 3 letter codes and show departure and arrival port, ie SYDBNE means departing from Sydney and arriving in Brisbane.

- Columns C to G show the maximum inventory we can sell on each particular route for all five products.

- Columns H to L show the number of units sold on each particular route for each product. Sold data is picked up from the sheet 'Sold' where all sales are entered. Sales can be for a product on a specific route, ie 3,000 itinerary confirmations for passengers flying along the SYDBNE route, or they can be blanket purchases, for example 5,000 itinerary confirmations for any passengers flying out of Sydney, regardless of their destination, or 4,000 itineraries for passengers flying into Brisbane, regardless of the port they flew out of.

- Columns M to Q show the remaining number of units available for sale on each particular product.

Here's where it gets tricky...

Columns T to AE show 2 percentages for each product, called dep (departure) and arr (arrival). Dep shows what percentage of people departing from that particular port are arriving into that destination port. The second shows what percentage of people arriving at that destination port are departing from that departure port. This is necessary so that I can work out how many units to allocate to any particular port when a client makes a blanket purchases, for example, when they want 10,000 itineraries for passengers departing from Sydney or 12,000 coming into Brisbane. Working out these percentages for each individual product is easy using an array formula to reference the figures in the full inventory columns.

Here's where I start pulling my hair out: When the number of available units for any product/route in columns M to Q falls below 25, I do not want any more units being allocated to that route. For example, if someone purchases 10,000 itineraries for any passengers flying out of Adelaide and there are less than 25 available itineraries on the ADLBNE route, I don't want any units allocated to the ADLBNE route and I want all other routes coming out of Adelaide to have their percentages adjusted accordingly and future sales distributed amongst all other routes flying out of Adelaide accordingly.

I have no idea how to do this. I can't change percentages based on units sold because that creates a circular reference. Even if I made the circular reference work by limiting iterations to one, units sold on that route would instantly fall to zero.

I need to cap allocation to individual routes once available falls below 25, keep anything already allocated to them and distribute any further unit sales amongst the other routes.

I have a feeling I'll need some VBA code to handle this issue because I can't even begin to work out how to do it with formulas.

Sorry for the confusing explanation. I'm happy to post a copy of the workbook if anyone is willing to have a look at it.

Thanks in advance!