Show lowest missing number in a sequence w/repeating numbers and blanks

I have a program that will be referencing this sheet so I can't have any VBA/macros or sorting involved. I just created a basic example of what I need and attached it. I can rearrange the formulas from there. I am looking for a formula in D2 that will look at column A and tell me the lowest number that is missing.It can reference column B or I don't need that all. The numbers will range from 1 to whatever in sequencial order.

There is other data that will be in this sheet, but people will be entering the number truck they placed each order (row) into (Column B). So there will be several rows/orders in the same numbered truck. There will be some that aren't in a truck yet. These trucks are transfer trucks from one facility to another and each truck is filled on different days. So for example once truck number 1 is used the next person will see they need to use truck number 2 for their orders and so on. There is a "use next" cell, but if someone uses the wrong number, in this case let's say they put in an 8, I want to say they are missing a 6 in D1.


Thanks for any help on this. I have tried several different ways, but have been unable to get the desired results.

I notice that some truck numbers are duplicated. I assume this means multiple orders went on that truck so truck 4 carried orders 123, 5151 and 55. The question I have is how do I know that a truck is filled? Right now there are 5 orders on truck number 5. How do I know when to stop using this truck and move onto truck number 6?

Also how do I know that I can't add another order to truck number 4?

Anyway, why would VBA invalidate using this sheet? You could put a change event on column B that will provide data validation and figure out the missing number without any manual intervention other than the user entering a number.

If all you want is the missing number, try this formula: =IF(MAX(B2:B32)>LARGE(B2:B32,2)+1,LARGE(B2:B32,2)+1,"").

What this formula does is find the second largest number using LARGE (in this case also 5) and if the number being added (found by MAX) is more than one more than it, it suggests one more than it. So it is blank if you are adding any number up to the next number allowed.

WHER's solution worked fine using ver. 2010, the WORKDAY.INTL function was added as of the 2003 version. As a test change, for example, the values of B4:B5 in his attached file to 7 and it will find that the number 3 truck has not been used.