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!

Unfortunately your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem.....

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Hi,

No the title is fine as it is now on the original. Thanks for the change.

Hi,

Would you upload the workbook so that we have something to work with. Cheers.

Hi

Now that we have the production workbook rather than the example, would you also update your earlier narrative, and refer to the cells/columns on this latest.