Consecutive numbers

[Sparky]

I have a column with 25 consecutive numbers, example 1 to 25. How do I highlight a cell or cells if someone changes one or more of the numbers in the column and breaks the succession? Example, if the numbers 1 to 25 are in cells A1:A25 and someone types the number 32 in A1 I need A1 to be highlighted as in conditional formatting.

 

[Dragracer]

Good catch Yogi

Like your formula too, very straight forward.

 

[Sparky]

Yogi

Well spotted I never thought of that occuring. How do I get it to work bearing in mind that my data is located in cells E3:E27 and also through to column DG.

Thanks for your interest

 

[Yogi Anand]

On 2002-10-12 21:49, Sparky wrote:
Yogi

Well spotted I never thought of that occuring. How do I get it to work bearing in mind that my data is located in cells E3:E27 and also through to column DG.

Thanks for your interest

Hi Sparky:

The data being in cells E3:E27 is no problem. Just change the CF formula to:

=E3<>row(E3)-2 Patterns|Color|Red for all the cells from E3 to E27

I don't understand what you mean by 'throught to column DG' -- you may have to explain that a little further.

But see if the first part works for you -- and then let us take it from there.

Regards!

Yogi

 

[Sparky]

Yogi

It works fine.
Column DG is just where some of my other data is located.

Cheers

Also to Bolo, Dragracer and Wob

 

[Sparky]

Yogi

The formula =E3<>ROW(E3)-2 works fine with the conditional format. However when applied to the following =K3<>ROW(K3)-2 the CF fills the range K3:K27 with the colour red even though the numbers are still in sequence.

For the record I have 18 lots of sequenced numbers.

Thanks again
This message was edited by Sparky on 2002-10-13 14:37

 

[bolo]

For the CF for K th column just add the offset, or 23 in your example, to the condition formula.

By offest i mean a number to compensate for the fact that even though the numbers are still consecutive the row numbers are not.
The K3 condition is obviously false because you are asking is 3 equal to 26. the 23 will fix this problem. Sorry for the confusion.
This message was edited by bolo on 2002-10-13 14:21

 

[Sparky]

Bolo

Could you repost please and also advise where to place 23 in the CF below.
=K3<>ROW(K3)-2

 

[bolo]

in your CF instead of K3<>rows(K3)-2 use
K3<>rows(K3)+23

Please edit your post above. the html is too big

HTH

 

[Yogi Anand]

Hi Sparky, and Bolo:

Bolo: I did not understand your statement regarding adding the offset;

I believe that Sparky has numbers in rows E3:E27 ... so =E3<>row(E3)-2 works

and

I belive Sparky has labels (text) in cells K3:K27 (even though the entries look like numbers ... so =K3<>row(K3)-2 does not work

A common fix to take care of whether Sparky has them as numbers or Text would be to modify the CF formula to:

=K3+0<>row(K3)-2

Sparky, implement this fix and please post back if it works for you ... otherwise explain a little further and let us take it from there.

Regards!

Yogi

 

[Sparky]

Yogi, Bolo

The entries are numbers, I tried your fix anyway and it filled the range K3:K27 with the colour red.
The first sequence of numbers 1-25 with the CF formula =E3<>ROW(E3)-2 works OK.
The second sequence of numbers 26-50 with the CF formula =K3<>ROW(K3)+23 works OK.
The third sequence of numbers 51-75 (Q3:Q27)
I am lost.
Altogether I have 18 lots of sequenced numbers starting at E3:E27 and ending at DC3:DC27 (426-450).
Any further suggestions appreciated