text = text block

[rjmckay]

Hello... I am trying to make a spreadsheat that will stop users fro
entering an entry already entered.

I have a Serial # column...

these serial #'s can be any combo of letters and numbers...

how do I get this thing to not allow you to enter the same seria
number as used in above in the column.

Or at least pop up a box saying "hey... this already exists" etc.

Thank you

 

[Paul B]

rjmckay, use data validation, for column A, select column A then data,
validation, settings, allow, custom, formula = COUNTIF($A:$A,A1)<2 , error
alert, put your message here, now when you try to put a duplicate in column
A you will get the message

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[rjmckay]

Hey, thank you!

I quickly and egarly went to the spreadsheet to try it...

I put in the =COUNTIF($B:$B,B1)<2

(B is the column I need.... and the data starts on row 4) not sur
what that ,B1 means though or the <2 for that matter.

And I put my error mesage in.

However after I go to test it.... nothing happens... it just lets m
fill in that cell just like it always did.

Am I doing something wrong here?

R

 

[Paul B]

If you want to use a range in column B and not the whole column use
something like this, select your range and put this in
=COUNTIF($B$4:$B$30,B4)<2 , change B$30 to the end of your range

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Harlan Grove]

Hello... I am trying to make a spreadsheat that will stop users from
entering an entry already entered.

I have a Serial # column...

these serial #'s can be any combo of letters and numbers...

how do I get this thing to not allow you to enter the same serial
number as used in above in the column.

....

Unless these serial numbers are generated by some other program or process,
you have a more fundamental problem. Computers are much, much better than
humans at generating distinct serial numbers. A macro could generate serial
numbers guaranteed to differ from others already in the workbook and would
relieve users from having to enter them. Wouldn't that be preferable?

Putting this another way, forcing humans to generate more or less random
strings of letters and digits that aren't already in a given list is an
extreme form of user-hostile design.