I want to check a range for available serial numbers.

The numbers run from 1001 and up, but as the cases close, some migh
become available again.

E.g. 1004 could be deleted from the range (becoming available again
while 1005 and so on is still taken...

How do I check the range "case" for lowest available number starting a
1001, and return it as an integer?

Thank

--
Message posted from http://www.ExcelForum.com

I'm assuming you have a column of data that looks like numbers but they are
really entered as text.
A1: '1001
A2: '1002
A3: '1003
....
A10: '1010

You can place the following ARRAY formula in cell C1 to get the lowest
value.
C1: =MIN(VALUE(A1:A10))
--Important-- Finish typing the entry with: Ctrl Shift Enter

If done correctly, the value will be displayed and the text in the EditBar
will look like this:
{=MIN(VALUE(A1:A10))}
Note the curly brackets.

Troy

"Steff_DK " wrote in message
...
I want to check a range for available serial numbers.

The numbers run from 1001 and up, but as the cases close, some might
become available again.

E.g. 1004 could be deleted from the range (becoming available again)
while 1005 and so on is still taken...

How do I check the range "case" for lowest available number starting at
1001, and return it as an integer?

Thanks


---
Message posted from http://www.ExcelForum.com/

Hi
try the following array entered formula (entered with
CTRL+SHIFT+ENTER) to get the lowest available free number:
=MIN(IF(COUNTIF(A1:A1000,ROW(INDIRECT("1001:30000" )))=0,ROW
(INDIRECT("1001:30000"))))



-----Original Message-----
I want to check a range for available serial numbers.

The numbers run from 1001 and up, but as the cases close,
some might
become available again.

E.g. 1004 could be deleted from the range (becoming
available again)
while 1005 and so on is still taken...

How do I check the range "case" for lowest available
number starting at
1001, and return it as an integer?

Thanks


---
Message posted from http://www.ExcelForum.com/

.

Hi
in this case no need for VALUE and an array formula.
MIN(A1:A10)
should return the same value

But this will get the lowest USED number and not the
lowest FREE number :-)

Frank
-----Original Message-----
I'm assuming you have a column of data that looks like
numbers but they are
really entered as text.
A1: '1001
A2: '1002
A3: '1003
....
A10: '1010

You can place the following ARRAY formula in cell C1 to
get the lowest
value.
C1: =MIN(VALUE(A1:A10))
--Important-- Finish typing the entry with: Ctrl Shift
Enter

If done correctly, the value will be displayed and the
text in the EditBar
will look like this:
{=MIN(VALUE(A1:A10))}
Note the curly brackets.

Troy

"Steff_DK "
wrote in message
...
I want to check a range for available serial numbers.

The numbers run from 1001 and up, but as the cases
close, some might
become available again.

E.g. 1004 could be deleted from the range (becoming
available again)
while 1005 and so on is still taken...

How do I check the range "case" for lowest available
number starting at
1001, and return it as an integer?

Thanks


---
Message posted from http://www.ExcelForum.com/



.

Frank,

The OP's statement of: "and return it as an integer" made me think that they
have text values. I was careful to state my assumption about the data being
text entries. I tried to show the text values by using the notation with an
apostrophe leading the numbers (e.g. '1001).

If the user simply has numeric entries in the cells then a MIN() function
will suffice. Otherwise the array formula is one approach to delivering the
result.

This medium makes it difficult at times to fully understand the starting
conditions. Thanks for the double-check.

Troy

"Frank Kabel" wrote in message
...
Hi
in this case no need for VALUE and an array formula.
MIN(A1:A10)
should return the same value

But this will get the lowest USED number and not the
lowest FREE number :-)

Frank
-----Original Message-----
I'm assuming you have a column of data that looks like
numbers but they are
really entered as text.
A1: '1001
A2: '1002
A3: '1003
....
A10: '1010

You can place the following ARRAY formula in cell C1 to
get the lowest
value.
C1: =MIN(VALUE(A1:A10))
--Important-- Finish typing the entry with: Ctrl Shift
Enter

If done correctly, the value will be displayed and the
text in the EditBar
will look like this:
{=MIN(VALUE(A1:A10))}
Note the curly brackets.

Troy

"Steff_DK "
wrote in message
...
I want to check a range for available serial numbers.

The numbers run from 1001 and up, but as the cases
close, some might
become available again.

E.g. 1004 could be deleted from the range (becoming
available again)
while 1005 and so on is still taken...

How do I check the range "case" for lowest available
number starting at
1001, and return it as an integer?

Thanks


---
Message posted from http://www.ExcelForum.com/



.

I have the range A:A named "case".
It contains the numbers 1001, 1002 etc...
I want a function to return the lowest number between 1001 and infinit
that IS NOT in the range "case"

I got this from ~x (thanks!) but I want to do this without using an
cells. Have tried goofing around with the below, but can't get it t
work...

~x wrote:

Assuming that your initial serial numbers are in A1:A5, insert thi
formula in A6
=getNAN(=getNAN($A$1:A6)

The getNAN UDF IS:

Public Function getNAN(ByVal MySerial As Range) As Variant
min = Application.WorksheetFunction.min(MySerial)
max = Application.WorksheetFunction.max(MySerial)
For i = 1 To max - min
If min + i < WorksheetFunction.Small(MySerial, i + 1) Then
getNAN = min + i
Exit Function
End If
Next i
If IsEmpty(getNAN) Then getNAN = max + 1
End Functio

--
Message posted from http://www.ExcelForum.com

Not the most elegant way, but here's how I fixed it:

Dim iSer As Integer
Dim iSertwo As Integer
Dim FoundCell As Range
iSer = 1000
iSertwo = 1000
Set FoundCell = Range("A1")

Do Until FoundCell Is Nothing
iSer = iSertwo
iSertwo = iSer + 1

Range("case").Select
Set FoundCell = Range("case").Find(What:=iSertwo, After:=ActiveCell
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext
_
MatchCase:=False)
Loop
End With
Msgbox iSertw

--
Message posted from http://www.ExcelForum.com