• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation with two conditions

I need to ensure that only certain ID's are entered and that a given ID is entered only once.
Pleas see the attached workbook.
 

Attachments

  • DV_test.xlsx
    11.4 KB · Views: 9
I think I got it solved myself by using the following data validation formula in D6:

=(SUM(OR(D6=ID))=1)*(COUNTIF(D$6:D6;D6)=1)
 
The logic of you formula ( SUM?,OR?) totally escapes me
Based on your D6:D13 range following does the trick =AND(MATCH($D6,ID),(COUNTIF($D$6:$D6,$D6)=1)) applied to D6:D13
 
You were asking for data validation
The value in D10 ( using a formula ) returns FALSE. Entered in the Data Validation wizard it would trigger an error. Thus FALSE is correct
As for D1, you are right I forgot the exact match switch =AND(MATCH($D6,ID,0),(COUNTIF($D$6:$D6,$D6)=1)) which you can use uin the DV now
 
I haven't mentioned anywhere that FALSE in D10 is incorrect, cf. the 0-result of my own formula in E10.

As for D12 I guess you mean instead of D1 as you write.
 
Yep, D12 that is
No you didn't mention it but other members might think something's wrong. ( as there is no DV implemented as such)
This being said, does this answer your query?
 
This is intended to provide a validation dropdown for each entry.
(It uses a large helper range and the LET and FILTER functions from MS365)
Code:
= LET(
  otherIDs,  IF(Table1[Seq]<>Table1[@Seq], Table1[ID]),
  available, ISERROR(XMATCH(ID, otherIDs)),
  IF(ISERROR(Table1[@Seq]), "", TRANSPOSE(FILTER(ID, available))) )
 

Attachments

  • DV_test_2 (PB).xlsx
    15.5 KB · Views: 6
vletm and Peter

Your solutions of course work very well so thanks to both of you.
However I think I will go with Pecoflyer's solution as it also works and I understand the formula immediately. I have never been that good at VBA and I am just starting to learn about LET.
 
Back
Top