Car queue formula needed

[grogmi1]

Hi, I have an Excel sheet which lists car numbers in one of its columns (not in sequence, they are random and need to be). These numbers are in column G2 to G180.

I have a formula running on the sheet which removes the next available car number from this list and leaves the cell where it was blank.

What I need is a formula that will look down the list in column G, ignore the blank cells and display the next available car number in cell A1.

Any help would be much appreciated!!
Thanks

 

[Ruddles]

Does it have to be a formula or will a custom function (written in VBA) be acceptable?

 

[Arcticwarrio]

=large(g2:g180,1)+1

 

[jasonb75]

Your question could be interpretedin 1 of 2 ways, either

Array formula, confirm with Shift Ctrl Enter

=index(G2:G180,small(if(G2:G180<>"",row(G2:G180),1))

or regular formula

=small(G2:G180,1)

 

[grogmi1]

Either, a VB function would be great, I'm a complete novice with VB but any help would be great thanks

 

[Arcticwarrio]

I think i know what you want now

so the numbers are all in a random order and you want to find the lowest missing number?

 

[grogmi1]

Hi,

No, sorry, the numbers are in random order because each car has its own unique number and they are in a queue e.g. 1, 15, 186, 3, 7 etc etc. When car 1 gets a job it is removed from the queue so 15 would be next but there is a blank cell where 1 was.

I'm looking for a formula (or other means) that will read the list of cars and give me the number ofnext available car in cell A1.

Thanks again

 

[jasonb75]

Hi,

No, sorry, the numbers are in random order because each car has its own unique number and they are in a queue e.g. 1, 15, 186, 3, 7 etc etc. When car 1 gets a job it is removed from the queue so 15 would be next but there is a blank cell where 1 was.

I'm looking for a formula (or other means) that will read the list of cars and give me the number ofnext available car in cell A1.

Thanks again

see reply #4

 

[grogmi1]

Hi,

I've tried both solutions in message # 4 but the 1st one stated there were few to arguments ??? and the 2nd one would only give the answer '1' in cell A1.

Regards

 

[Arcticwarrio]

What formula do you use for removing the car?