Show number sequence breaks?

[Teri M.]

Is there a way to format a spreadsheet so Excel will automatically show a
break a sequence of numbers? I have a very basic spreadsheet the purpose of
which is to make sure we eventually receive all the numbered items. Showing
a break in sequence will do the job. Kind of like you bank statement when
they list your checks and put and asterisk indicating a break in the check
number sequence.

 

[Bernard Liengme]

In A1:A15 I entered the series 101, 102, ... 115
Then I deleted A7, A11 and A12 (numbers 107,111,112)
In B1 I entered the formula

Now cells B8 and B12 (next to numbers 108 and 113) have *
If I wish I can deleted rows 7, 11 and 12 and have the same effect

Alternatively, select all the values except the first one and open the
Conditional Formatting dialog (it in the Format menus); use the formula
=A2<>A1+1 and set a format to highlight the cell

best wishes

 

[ShaneDevenshire]

Hi,

If Bernard's answer is not what you need, please show us a sample of the
before and after, show we know what you are doing.

 

[Teri M.]

10805650
10805651 10805701
10805652
10805653
10805654
10805655
10805656
10805657
10805658
10805659
10805660
10805661
10805662
10805663 10805713
10805664 10805714
10805665 10805715
10805666 10805716
10805667 10805717
10805668 10805718
10805669 10805719
10805670 10805720
10805671
10805672
10805673
10805674
10805675
10805676
10805677
10805678 10805728
10805679
10805680
10805681
10805682
10805683 10805733
10805684
10805685
10805686
10805687
10805688
10805689
10805690
10805691
10805692 10805742
10805693 10805743
10805694 10805744

10805746
10805747

10805699 10805749

All I want to do is enter the report numbers, "sort" the columns in
numerical order and format Excel indicate to me when there is a break in
sequence so I do not have to go through row by row to find them. Teri

 

[ShaneDevenshire]

Hi,

Suppose you are looking in column A for that break, where you first number
is in A1.
1. Highlight A2:A100 or however far down your data goes
2. Choose Format, Conditional Formatting, pick not equal to from the second
drop down, and enter =A1+1 in the third box,
3. Click the Format button and choose a color.
Click OK twice. Any numbers not in sequence will be colored.

If this helps, please click the Yes button.

 

[Teri M.]

Hello,

My spouse doesn't call me "Techno Teri" for nothing. I cannot get it to
work. When I follow your instructions, it highlights all the cells even when
they are in sequence. Thank you for your help. I need to move on. This is
not critical. It was to make it easier to recognize when a report number was
missing.

Thank you again.
Teri