Hi Ashish,

I've been not active for some months after posting two questions whose answer were teriffic. Now i've one question regarding validation (actually a modified version of previous question regarding validation). Here is the table

now what I want is that duplication of X/Y and X/Z can be allowed in their respective rows for 1 time but duplication of other valueslike A,B,C, Z,Y and so on (Z, Y if exist alone) should not be allowed. Please tell how to applying validation without using any VBA code.

Thanks

Hi,

Suppose there are 5 persons whose names are ajay, sachin, sumit, sandy, gyan

I can make a combination of ajay with sachin and gyan not with others. So in some rows i can enter ajay/sachin (together with /) butnot more than two times while in some rows i can ajay/gyan but not than two times. Other name can't be repeated.

plz note that the number of people are can more than 5.

Hope this is more clear.

Regards,

Hi,

You may refer to my workaround - Question 13 at the following link - http://ashishmathur.com/knowledgebase...

Do let me know how this works for you.

Hi,

You may try this approach. In cell H13, enter "Entries" (without quotes) and in cell I13, enter "frequency of entry per row" (without quotes). In H14:H20, enter X/Y,X/Z,a,b,c,y,z. In I14:I20, enter 2,2,1,1,1,1,1. Click on cell A4 and go to Data > Validation > List. In the Sourece box, enter =$H$14:$H$20. Copy this to the right (till where a user woudl be allowed to enter data). Now click on cell B4 and go to Home > Styles > Conditional formatting > New Rule > Use a formula to determine which cells to format. In the while box, which appears there, enter the following formula

=(OR(AND(ISNUMBER(SEARCH("/",B4)),SUMPRODUCT(1*(ISNUMBER(SEARCH("/",$A4:A4))))=0,COUNTIF($A4:B4,B4)<=VLOOKUP(B4,$H$14:$I$20,2,0)),AND(ISNUMBER(SEARCH("/",B4)),SUMPRODUCT(1*(ISNUMBER(SEARCH("/",$A4:A4))))=1,COUNTIF($A4:B4,B4)<=VLOOKUP(B4,$H$14:$I$20,2,0),COUNTIF($A4:A4,B4)=1),AND(ISERROR(SEARCH("/",B4)),(COUNTIF($A4:B4,B4)<=VLOOKUP(B4,$H$14:$I$20,2,0)))))=FALSE

Apply the format as Red font (or whatever you wish). Entries which do not specify the data entry rule will get coloured in red

If you are wondering why i have not entered this formula in Data > Validation > All > Custom, the reason is the length of thr formula. There is a limitation on the length of a formula which one can write in Data Validation.

Please keep in mind that you must enter data in in continuous cells from left to right i.e. enter data in A4, B4, C4, D4 etc. Do not enter data in A4, D4, B4 etc.

Hope this helps. Do let me know how this works.