Is there an excel function to perform number validation? 2

[tcolan]

Hello,

Let's say I have an excel worksheet with a column called "ID". As people enter IDs, I want the cell to perform data validation on the column (all of the cells above and below the current cell) to make sure that each ID entry is unique. I also need to be able to add rows to the range, etc.

Example:
ID
1
2
3
3 <- This would generate an error and prompt the user to enter another value.

Thanks in advance for your help,
Tom

 

[straybullet]

The only way Ive ever seen of achieving this kind of validation that I know of is in Access. Setting the field property to NOT allow duplicates... If the numbering is always sequential, you can always set up the field as an autonumber. For interaction, you can set up a form for entry of new records.

 

[KenWright]

Assuming your data was starting in A1, select all of Col A and do Data / Validation / Custom then paste the following formula into the box:-

=COUNTIF(A:A,A1)=1

Downside is that DV can be bypassed by simply pasting into any of those cells.

You might also want to take a look at data / Form for adding new rows. It respects Data Validation settings and will error if used to try and enter duplicate values.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[buitenhek]

This may be oversimplified. Set the column at size that will accommodate you requirements.


ID countif
1 1
2 1
3 1
4 1
5 Input Error 2
6 Input Error 2
6 Input Error 2
5 Input Error 2
8 1
9 1
10 1

ID countif
1 =IF(OR(C4=1,C4=0),&quot;&quot;,&quot;Input Error&quot =COUNTIF($A$4:$A$30,A4)
2 =IF(OR(C5=1,C5=0),&quot;&quot;,&quot;Input Error&quot =COUNTIF($A$4:$A$30,A5)
3 =IF(OR(C6=1,C6=0),&quot;&quot;,&quot;Input Error&quot =COUNTIF($A$4:$A$30,A6)
4 =IF(OR(C7=1,C7=0),&quot;&quot;,&quot;Input Error&quot =COUNTIF($A$4:$A$30,A7)
5 =IF(OR(C8=1,C8=0),&quot;&quot;,&quot;Input Error&quot =COUNTIF($A$4:$A$30,A8)
6 =IF(OR(C9=1,C9=0),&quot;&quot;,&quot;Input Error&quot =COUNTIF($A$4:$A$30,A9)
6 =IF(OR(C10=1,C10=0),&quot;&quot;,&quot;Input Error&quot =COUNTIF($A$4:$A$30,A10)
5 =IF(OR(C11=1,C11=0),&quot;&quot;,&quot;Input Error&quot =COUNTIF($A$4:$A$30,A11)
8 =IF(OR(C12=1,C12=0),&quot;&quot;,&quot;Input Error&quot =COUNTIF($A$4:$A$30,A12)
9 =IF(OR(C13=1,C13=0),&quot;&quot;,&quot;Input Error&quot =COUNTIF($A$4:$A$30,A13)
10 =IF(OR(C14=1,C14=0),&quot;&quot;,&quot;Input Error&quot =COUNTIF($A$4:$A$30,A14)
Good Luck


Regards
Peter Buitenhek
Profit Developer.com