Show Next Number in sequence
 

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