Assuming "Order Number" is in cell A1 you can use this formula to display the
next available Number. This only works because you are going in sequential
order.

=INDEX(A1:B5,2+COUNTIF(B1:B5,"In Use"),1)

"Steven Brookes" wrote:

i have a sheet showing an order number and it availablility:

Order Number Status
BIO10001 In Use
BIO10002 In Use
BIO10003 Available
BIO10004 Available

etc etc

This sheet is a 'tool' for the main sheet where users allocate order numbers
to a specific order, they choose the next available order number from the
next 'Available' one as above. The 'Status' is set by using a COUNTIF which
displays in results in a hidden field. Then the 'Status' field it is is based
on =IF(I114=1,"In Use","Available")

What i would like is to display the next available order number on the main
sheet, say in a cell at the top i.e "The Next Available Order Number is" my
question is how do i get excel to display the next 'Available' order number
from my list automatically