Missing Numbers??

You can use conditional formatting.
If the first number in your list is in cell A1 then in cell a2 set the conditonal format:
If cell value is not equal to A1+1 then [set a colour format].

Then select cell A2 and the range to the end of your list and go:
conditional format OK

The easiest way I know of is to use a helper column, as follows:
Assuming your data starts in A2 (A1 is heading row?), and that you have a blank column (we'll use column B for this example). The formula in cell B2 could be as simple as (a more elegant solution follows). Copy this down to the last row of data. Any cell in column B that doesn't equal 1 indicates that the number in the next row down skipped something.
Now, for a more elegant solution, your first formula would be in cell B3:
Then you could just glance down the column for the word "Missing". You could also filter on the word "Missing", but since that hides the rest of the rows, you might not want to use a filter.
Hope this helps,
Cindy

I'll try that

Thanks a lot for the help

Hi

I'm looking for a formula or something maybe someone can help me. Let me explain. I usually have a long list of numbers and I need a way to know when at some point of the list, I have skipped a number. Something like this:

102
103
104
Missing Number Here****
106
107
Missing Number Here****
110

Something like that. Is there a way to do this with a formula?

Thanks a lot for the help

Click to expand...

E1:

=MIN(A2:A7)

E2:

=MAX(A2:A7)

E3:

=(E2-E1+1)-ROWS(A2:A7)

or:

=(E2-E1+1)-COUNT(A2:A7)

F2, copied down:

=IF(ROWS($F$2:F2)<=$E$2-$E$1+1,$E$1+ROWS($F$2:F2)-1,"")

G2:

Control+shift+enter...

=IF(ROWS($G$2:G2)<=$E$3,INDEX($F$2:$F$10,SMALL(IF(1-ISNUMBER(MATCH($F$2:$F$10,$A$2:$A$7,0)),ROW($F$2:$F$10)-ROW($F$2)+1),ROWS($G$2:G2))),"")

and copy down.