Identify missing record numbers

I have a work sheet that has sheet numbers from 1 to 2800. I now need to
identify those sheet numbers that have been deleted. See the example below.

Is there a formula for this?

Sheet Project Name
Number
1 Abundant Life Christian Center
2 Abundant Life Christian Center
3 Abundant Life Christian Center
6 Admiral Byrd Drive
7 Admiral Byrd Drive
8 Admiral Byrd Drive
10 Aerocenter Lot 4
11 Aerocenter Lot 4
12 Aerocenter Lot 4
14 Airport Warehousing at Arbor Court
15 Airport Warehousing at Arbor Court


TIA

Where are the sheets that you are validating?

"Earl" <[email protected]> wrote in message
news:[email protected]...
>I have a work sheet that has sheet numbers from 1 to 2800. I now need to
> identify those sheet numbers that have been deleted. See the example
> below.
>
> Is there a formula for this?
>
> Sheet Project Name
> Number
> 1 Abundant Life Christian Center
> 2 Abundant Life Christian Center
> 3 Abundant Life Christian Center
> 6 Admiral Byrd Drive
> 7 Admiral Byrd Drive
> 8 Admiral Byrd Drive
> 10 Aerocenter Lot 4
> 11 Aerocenter Lot 4
> 12 Aerocenter Lot 4
> 14 Airport Warehousing at Arbor Court
> 15 Airport Warehousing at Arbor Court
>
>
> TIA

Hi!

One way based on your small sample:

Assume the "consecutive" numbers are in column A, A1:A11

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(ROW($1:$15),SMALL(IF(COUNTIF(A$1:A$11,ROW($1:$15))=0,ROW($1:$15)),ROWS($1:1)))

Copy down until you get #NUM! errors meaning the data has been exhausted.

Based on your sample, will return:

4
5
9
13
#NUM!

ROW($1:$15) represents the numeric range of the posted sample. If you
actually have 1:2800 then change ROW($1:$15) to ROW($1:$2800)

Biff

"Earl" <[email protected]> wrote in message
news:[email protected]...
>I have a work sheet that has sheet numbers from 1 to 2800. I now need to
> identify those sheet numbers that have been deleted. See the example
> below.
>
> Is there a formula for this?
>
> Sheet Project Name
> Number
> 1 Abundant Life Christian Center
> 2 Abundant Life Christian Center
> 3 Abundant Life Christian Center
> 6 Admiral Byrd Drive
> 7 Admiral Byrd Drive
> 8 Admiral Byrd Drive
> 10 Aerocenter Lot 4
> 11 Aerocenter Lot 4
> 12 Aerocenter Lot 4
> 14 Airport Warehousing at Arbor Court
> 15 Airport Warehousing at Arbor Court
>
>
> TIA

The 'sheets' are scanned drawing files that we catalog by a sheet number
label attached to the drawings.

"Barb Reinhardt" wrote:

> Where are the sheets that you are validating?
>
> "Earl" <[email protected]> wrote in message
> news:[email protected]...
> >I have a work sheet that has sheet numbers from 1 to 2800. I now need to
> > identify those sheet numbers that have been deleted. See the example
> > below.
> >
> > Is there a formula for this?
> >
> > Sheet Project Name
> > Number
> > 1 Abundant Life Christian Center
> > 2 Abundant Life Christian Center
> > 3 Abundant Life Christian Center
> > 6 Admiral Byrd Drive
> > 7 Admiral Byrd Drive
> > 8 Admiral Byrd Drive
> > 10 Aerocenter Lot 4
> > 11 Aerocenter Lot 4
> > 12 Aerocenter Lot 4
> > 14 Airport Warehousing at Arbor Court
> > 15 Airport Warehousing at Arbor Court
> >
> >
> > TIA
>
>
>