Formula to deduct a given value from the BOTTOM of a range

[garwil]

Hi,
I am developing a booking system mock-up and have a range of values from cell A1-B4 representing booking codes such as "A=(Highest rate), "B" (next lower rate) etc. Column "B" has the amount of inventory left for that specific code.. so the table looks as follows:
Columns

<TABLE style="WIDTH: 164pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=219><COLGROUP><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4973" width=136><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><TBODY><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 102pt; HEIGHT: 11.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=15 width=136>Booking code</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl74 width=83>Available</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl76 height=17>Y</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl77>65</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78 height=17>B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl79>40</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78 height=17>H</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl79>15</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl78 height=17>V</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl79>0</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl75 height=18>Total</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80>120</TD></TR></TBODY></TABLE>

If a booking is made I need a formula to deduct the required inventory booked from the lowest Booking Code Value first and move progressively upwards. E.g. If a booking is made for 16 people then the formula would first look at the bottom of the range (B4), see there is a zero balance and move upwards, see there is enough inventory available and deduct 15 from code "H" and 1 from code "B". Is this possible??
Any help will be greatly appreciated.

 

[garwil]

Thanks M... Will keep working on Tom's solution and see if I can figure it out...

 

[garwil]

Tom, thanks for this. I emulated on a new sheet and assigned a button to trigger the macro. Where in your code does it say where the columns and cells are located so that I can hange it to use in another sheet with many more flights assigend (I have 365 days and 8 flights per day so will like to assign this code to each flight and add some code to call up the particular flight requested by a customer as per a date? I'll get to this once I am au feit with VBS then I'll go to the next step...