Duplicate dates

Nathan B

New Member
Joined
Apr 18, 2002
Messages
35
What's the best way to keep a user from entering the same date twice in a selected group of cells?

Thanks
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
I've tried using this formula but I must not understand it completely. Can you give me a little more detail? Here's my exact situation. A user can enter dates in cells A5 through A19, C5 through C19, E5 through E19, G5 through G19, I5 through I19, K5 through k19, and M5 through M19. I don't want them to enter the same date twice in any of these cells. Please help me figure this out.

Thanks
 
Upvote 0
On 2002-04-21 17:40, Nathan B wrote:
I've tried using this formula but I must not understand it completely. Can you give me a little more detail? Here's my exact situation. A user can enter dates in cells A5 through A19, C5 through C19, E5 through E19, G5 through G19, I5 through I19, K5 through k19, and M5 through M19. I don't want them to enter the same date twice in any of these cells. Please help me figure this out.

Thanks

Setup Data Validation for A5 using...

=COUNTIF($A$5:$A$19,A5)+COUNTIF($C$5:$C$19,A5)+COUNTIF($E$5:$E$19,A5)+COUNTIF($G$5:$G$19,A5)+COUNTIF($I$5:$I$19,A5)+COUNTIF($K$5:$K$19,A5)+COUNTIF($M$5:$M$19,A5)<2

...and, apply to all cells in the ranges listed above.
 
Upvote 0
On 2002-04-21 17:40, Nathan B wrote:
I've tried using this formula but I must not understand it completely. Can you give me a little more detail? Here's my exact situation. A user can enter dates in cells A5 through A19, C5 through C19, E5 through E19, G5 through G19, I5 through I19, K5 through k19, and M5 through M19. I don't want them to enter the same date twice in any of these cells. Please help me figure this out.

Thanks

Set up custom data validation for
A5
with the formula:

=SUMPRODUCT(($A$5:$M$19=$A5)*(MOD(COLUMN($A$5:$M$19),2)<>0))<2

and copy the cell to A15;

for C5 by changing $A5 to $C5:

=SUMPRODUCT(($A$5:$M$19=$C5)*(MOD(COLUMN($A$5:$M$19),2)<>0))<2

and copy the cell to C15.

Repeat the procedure for the rest of the ranges of interest.

Aladin
 
Upvote 0
I tried Mark W's formula and it works great. I didn't try the other formula. Thanks to everyone for the help.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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