Validation/Duplication

[jrogers]

I need help writing a formula for validation of entries. On the include
file, rows 9 & 17 include truck numbers that our drivers use. Th
problem is, I would like my dispatcher to get an error message if h
enters the same truck number 2x in the same column (Example - cells D
& D17 both contain truck number 2668 ). I've tried to use th
Validation Rule, but it allows me to enter the same number in as man
cells as I want ?! The formula I used was "=COUNTIF(D3299,D3)=1"
but that doesn't work. Does anybody know a way of doing this ? An
help would be greatly appreciated !

Attachment filename: exceltip.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=54374

 

[Frank Kabel]

Hi
see
http://www.cpearson.com/excel/NoDupEntry.htm

 

[mzehr]

Hi
Go to Cell D3 and put this formula in the Data Validation
window
=COUNTIF($D$3:$D3,D3)=1
then copy the formatting of that cell all the way down
column D from cell 3 to whereever you want to end up.

The problem with your formula was that it was not set up
properly with respect to absolute and relative references.

See Debra Dalgleish web site for more details:
http://www.contextures.com/xlDataVal03.html

HTH

 

[jrogers]

Thank you , & it's real close. The formula works fine if entering truc
numbers from top to bottom , but if randomly entering truck numbers in
it doesn't work. As you can see, drivers are listed at the left & thei
"slot" takes up 8 cells. If a driver , let's call him Jim has a truc
assigned to him in cell D26 , we can then go to cell D10 & enter th
same number there also (which does not work in reverse).

I hope that's a clear enough description of the problem I'm having.

Thanks alot, I appreciate your responses