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.
Can this be done?

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2