I’m looking for any recommendations on an algorithm that could be used to assign volume from my “Qty Remaining” column to the appropriate cells in the “Actual Order” table. If you review my attachment, you’ll get a clearer picture of what I’m looking to accomplish.
Nevertheless, here’s a basic run down of what I’m looking for:
1. The quantities “Grand Total” columns in both the “Initial Request” and “Actual Order” table must end up matching.
2. The totals under each “Location” column can change, but the combined totals of all three locations will still need to match in
3. When a location’s “Actual Order” volume is reduced from its “Initial Request”, I can use a simple formula like this IF(H$6<=B$6,(B3/B$6)*H$6) to make an adjustment.
4. However, when the volume is increased, I’m at a loss on how to spread the remaining quantity. I must meet the location’s total without exceeding the item’s grand total.
Again, please refer to my attached file for a clear picture of what I’m looking for. Any recommendations on a formula that would handle this would be greatly appreciated. Thanks!