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