Duplicate Dates

drltr6

New Member
Joined
Dec 14, 2007
Messages
18
Office Version
  1. 2011
Platform
  1. MacOS
I have a worksheet that has 32 cells for inputing the date. I want an error message to show if someone duplicates a date. How can I use Data Validation to make this happen?
Suppose my date entry cells are R7, R67, R127 etc. Someone inputs 4/18/15 into cell R7, inputs 4/19/15 into R67 and 4/18/15 into R127. I would like to have an error message telling them that the date has already been input, and to please choose a different date.
Thanks in advance for any and all suggestions.:)
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
That didn't help because my dates are not in a single column list of consecutive cells.
 
Upvote 0
That didn't help because my dates are not in a single column list of consecutive cells.

As in the suggested solution, the COUNTIF function could still work for the entire range including the cells in between as long as the in between cells don't have dates.
 
Upvote 0
I tried that, but I have other cells in the same column with different data validation. Is there a way to make list of the cells that the dates are input that will still trigger a warning that the date is a duplicate?
 
Upvote 0
I tried that, but I have other cells in the same column with different data validation. Is there a way to make list of the cells that the dates are input that will still trigger a warning that the date is a duplicate?

The suggested solution does work if there are no other dates in between R7, R67, R127... etc. Is that the case?

I'm guessing you just haven't implemented it correctly.


  • Select cell R7
  • Ctrl+Click on R67 and R127....etc. This will select noncontiguous cells.
  • Ctrl+Click on R7 so it's the currently active cell of the selection
  • Select from the menu Data\ Data Validation
    • Allow: Custom
    • Formula: =COUNTIF($R$7:$R$127,R7)=1 (where $R$127 is the last selected cell)
 
Upvote 0
Another possible solution

Select the range R7, R67, R127 etc using the Ctrl key
Name it, say, MyRange (with the range selected type MyRange in the Name box and hit Enter)

Select R7
Data > Data Validation > Custom
Uncheck Ignore Blank
enter the the formula below
=INDEX(FREQUENCY(MyRange,R7),1)-INDEX(FREQUENCY(MyRange,R7-0.1),1)=1
Ok

With R7 selected type Ctrl+C to copy

Select MyRange (use the Name box)

Paste Special Validation

Hope this helps

M.
 
Last edited:
Upvote 0
Yeah! That worked perfectly! Thank you so much for your help!
 
Upvote 0
This also worked! Thanks to all who help with their suggestions. This is a great Excel community!!
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top