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

 

[jasonb75]

Sorry, had paranthesis in wrong place

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

Still needs to be array confirmed with shift ctrl enter.

 

[Arcticwarrio]

i think you need this from jason's formula

=INDEX(G2:G180,SMALL(IF(G2:G180="",ROW(G2:G180)),1))

 

[grogmi1]

The formula I use looks at the list of cars that have been used (on a different sheet) and then removes that car number from the queue.


=IF(ISNA(VLOOKUP(F2,Sheet1!A:A,1,FALSE)),"",VLOOKUP(F2,Sheet1!A:A,1,FALSE))

Thanks again!!

 

[Arcticwarrio]

Post #12 should work for what you need

 

[grogmi1]

Hi,

I've tried this formula in cell A1 but it just returns #Value? . . .any ideas ??

Thanks

 

[Arcticwarrio]

you need to press CTRL+SHIFT+ENTER instead of just ENTER to put the formula in

 

[grogmi1]

Fantastic!! It works, thanks Arcticwarrio and thanks for all your help and advice guys.