Hans Knudsen
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.
Pleas see the attached workbook.
Data Validation with two conditions
- Thread starter Hans Knudsen
- Start date
Hans Knudsen
Have You checked Your
When it can be true? ... when one value and range are same? ... then the range is one value?
Ps. There seems to be possible to do as below
Your thread has same features with https://chandoo.org/forum/threads/data-validation-for-wo-column.45528/.
Which both would do other ways ...
Have You checked Your
(D2=ID) ?When it can be true? ... when one value and range are same? ... then the range is one value?
Ps. There seems to be possible to do as below
Your thread has same features with https://chandoo.org/forum/threads/data-validation-for-wo-column.45528/.
Which both would do other ways ...
Hans Knudsen
Member
I just don't understand why the logic used i G6:G13 is not accepted in Data Validation.
Hans Knudsen
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)
=(SUM(OR(D6=ID))=1)*(COUNTIF(D$6:D6;D6)=1)
Hans Knudsen
Member
pecoflyer
Well-Known 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
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
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.
As for D12 I guess you mean instead of D1 as you write.
Hans Knudsen
Member
It does, yes. Thank you.
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)
(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))) )Hans Knudsen
Here is one more other kind of sample...
Here is one more other kind of sample...
Hans Knudsen
Member
Thank you Peter and vletm.
Please give me some time to (hopefully) understand your solutions.
Please give me some time to (hopefully) understand your solutions.
Hans Knudsen
Member
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.
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.