Prevent Consecutive Entries

I would like to design a frmua to prevent to have 4 consecutive entries
of the same name:

B4 = Peter or any name
C4 = Peter or any name
D4 = Peter or any name
or
B4 = Peter or any name
C5 = Peter or any name
D6 = Peter or any name

When I reach E7 = Peter (or any name) - I would like a validation
formula to prevent me from additing the person 4 times. This fromula
shouls be in the range of B4:BA69. The name could appear 29 times but
not in more that 3 a consecutive times.


--
JorgeAE

Try this:

Select B4:BA69, with cell B4 as the active cell

From the Excel main menu:
<data><validation>
Allow: Custom
Formula:
=AND(COUNTIF(OFFSET(B4,,-MIN(COLUMNS($B:B)-1,3),1,MIN(COLUMNS($B:B),4)),B4)<4,COUNTIF(OFFSET(B4,-MIN(ROWS($4:4)-1,3),,MIN(ROWS($4:4),4),1),B4)<4)

Note_1: In case screen wrap impacts the display, there are NO spaces in that
formula.

Note_2: Data Validation won't trap copy/paste errors, but it WILL catch
input errors

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"JorgeAE" wrote:

>
> I would like to design a frmua to prevent to have 4 consecutive entries
> of the same name:
>
> B4 = Peter or any name
> C4 = Peter or any name
> D4 = Peter or any name
> or
> B4 = Peter or any name
> C5 = Peter or any name
> D6 = Peter or any name
>
> When I reach E7 = Peter (or any name) - I would like a validation
> formula to prevent me from additing the person 4 times. This fromula
> shouls be in the range of B4:BA69. The name could appear 29 times but
> not in more that 3 a consecutive times.
>
>
> --
> JorgeAE
>