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.

Prevent Consecutive Entries
 

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