Display next available number

[corewin]

Hi I have a spreadsheet with sequential numbers from 100 thru 2300 in
column.
There will be numbers missing in it like #269 or #400 or #1350-#1420
I need a formula that looks at column A and then displays the nex
available number.
for instance
Let say column A has the numbers 1 - 10 but the number 4 and number 9 i
missing.
I want the formula to display the number 4
then if I enter in the number 4 in column A it will display the numbe
9.
Can this be done

 

[Claus Busch]

Hi,

Am Fri, 5 Oct 2012 19:01:14 +0000 schrieb corewin:

Hi I have a spreadsheet with sequential numbers from 100 thru 2300 in a
column.
There will be numbers missing in it like #269 or #400 or #1350-#1420
I need a formula that looks at column A and then displays the next
available number.
for instance
Let say column A has the numbers 1 - 10 but the number 4 and number 9 is
missing.
I want the formula to display the number 4
then if I enter in the number 4 in column A it will display the number
9.

try:
=SMALL(IF(ISNA(MATCH(ROW($1:$2500),(A$1:A$2500),)),ROW($1:$2500)),1)
and enter the array formula with CTRL+Shift+Enter


Regards
Claus Busch