Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent duplicate entries in Excel 2000 | Excel Discussion (Misc queries) | |||
How do you prevent duplicate entries in an excel spreadsheet? | Excel Discussion (Misc queries) | |||
Adding Consecutive Entries | Excel Discussion (Misc queries) | |||
Adding Consecutive Entries | Excel Discussion (Misc queries) | |||
Prevent duplicate entries | Excel Worksheet Functions |