Help with assigning individual ref numbers

**Richardmckevitt** · 09-20-2011, 08:35 AM

Hi,

I am wanting to crete a simple table whereby once I hae assigned a unique ref number once it cannot be re allocated.

Is there a formula or a way of doing this?

For example if I assign 123456789 as a ref num to a product caled Oranges, if someone else tries to use that 123456789 ref number then an error message etc would occur

**martindwilson** · 09-20-2011, 08:58 AM

use data validation
eg allow custom formula =countif(b:b,b1)=1
warning you can still paste in a value the same and validation will be ignored,

**ChemistB** · 09-20-2011, 08:59 AM

Assuming you are talking about manually entering Ref # down a column (let's say Col B). You can use Data Validation.
Select your range (i.e. B2:B1000), go to data validation> Use Custom

=COUNTIF($B$2:B2,B2)<2

Warning: Data validation only works for manual entries. It will not work if people cut and paste or you are using formulas.