Data Validation with two conditions

Hans Knudsen

New Member
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

• 11.4 KB Views: 9

Hans Knudsen

New Member
I just don't understand why the logic used i G6:G13 is not accepted in Data Validation.

Hans Knudsen

New Member
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)

pecoflyer

Active Member
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

Attachments

• 11.2 KB Views: 8

pecoflyer

Active Member
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

Hans Knudsen

New Member
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.

pecoflyer

Active Member
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)

Hans Knudsen

New Member
It does, yes. Thank you.

Most welcome

Peter Bartholomew

Well-Known Member
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

• 15.5 KB Views: 5

Attachments

• 22.7 KB Views: 7

Hans Knudsen

New Member
Thank you Peter and vletm.