Data Validation with conditions

[Fahad987]

Hi,

I am working on setting up custom data validation as data is entered by the user. I have two columns A and B. In column A, i enter today's date (not using today() function). It is a named range with name "myDate". In column B, i type in customer names. however, sometimes there are a lot of customer names to enter and one or two names get typed again. I want to apply data validation on column B such that for only today's entries, if i happen to enter any customers' name twice in column B, the data validation rule prompts me that it has already been entered. For now, i am trying to enter this into my custom data validation rule:
=IF(myDate=today(), Countif($B$2:$B$50,B2)=1, 0)
But it does not restrict any duplicate entries in column B. When I use this countif formula without the IF condition only on column B data range, it works fine.

My Current data structure:

ColumnA ColumnB
myDate CustomerNames
29-11-2021 ABC
29-11-2021 LMN
30-11-2021 ABC
30-11-2021 LMN
30-11-2021 XYZ
30-11-2021 ABC <--- Here it should give me error message but only based on today's datewise entries.

I need this date conditional check because for the next day, i will resume data entries from right beneath my today's entries. So help from forum members here would be really great.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=COUNTIFS($A$2:$A$50,A2,$B$2:$B$50,B2)=1

 

[Fahad987]

Hi & welcome to MrExcel.
How about

=COUNTIFS($A$2:$A$50,A2,$B$2:$B$50,B2)=1

Dear Fluff,

Thank you. it seems to be working. However, can you please explain the logic behind its working. Why would i include the column A i.e. current date into the countif statement. Firstly, because the date is repeating itself downwards for today's entries, and secondly, i am supposed to be checking for data validation in column B, not column A. i'd be grateful.

 

[Fahad987]

One more thing though. I am using Excel 2003. I tried your solution in Excel 2010 and it works. However, can you specifically help me out with respect to Excel 2003 version?

 

[Peter_SSs]

Try this for your column B Data Validation formula, starting at cell B2

=SUMPRODUCT(--(A$2:A$50&B$2:B$50=A2&B2))=1

You need column A in the checking process because it is okay (as I understand it) to enter the same name in column B on different days. It is only when you enter the same column B with the same column A date that is the problem.
Looking at your original example in post #1: If we only looked at column B then the 3rd entry in column B would have been disallowed since ABC had already been entered.
However, by including a check of the date column that third entry is allowed but the last entry is disallowed because the combination of data and customer has already been used.

 

[Fahad987]

Try this for your column B Data Validation formula, starting at cell B2

=SUMPRODUCT(--(A$2:A$50&B$2:B$50=A2&B2))=1

You need column A in the checking process because it is okay (as I understand it) to enter the same name in column B on different days. It is only when you enter the same column B with the same column A date that is the problem.
Looking at your original example in post #1: If we only looked at column B then the 3rd entry in column B would have been disallowed since ABC had already been entered.
However, by including a check of the date column that third entry is allowed but the last entry is disallowed because the combination of data and customer has already been used.

Unfortunately, this is not giving the desired result. Before applying data validation rule, i select the whole desired range in column B, then after entering the above formula, when i enter data in very first cell in column B, it gives me error message right away.

 

[Peter_SSs]

Before applying data validation rule, i select the whole desired range in column B

What is that range?

 

[Fahad987]

Range is from cell B2 to B50. but it gives me error in cell B2 as soon as i enter something in it. and it happens downwards as well in any other cell within the range where i try to type something.

 

[Peter_SSs]

Sorry, I cannot reproduce what you are describing. The Data Validation works fine for me.

 

[Fluff]

Peter's formula works for me as well, did you use exactly what Peter suggested, or did you change it to include your named range?
Not sure this will work any better but try

=SUMPRODUCT((A$2:A$50=A2)*(B$2:B$50=B2))=1