Serving the Quantitative Finance Community
Excel question
say I am creating a column called Invoice No. and dont wanna allow any repeated entries in that column. How can I use data validation to achieve this?
Excel question
Don't think you can. Excel is not very good at handling Primary Key like problems. Your best bet would be to use List validation, but I don't know how long a list can. You then reference a seperate table of available invoice numbers. Even this means you will still get duplicates, but at least they are consistent! You would then need to perform some other validation routine.To be honest build it in a database would be better. Even if you don't have MS Access / can't afford Access then you could build a DB in MYSQL and use Excel as the front end through ADO.
Excel question
Maybe have a column right next to the "Invoice No." column which does a lookup of past values to see if the value you just entered is a duplicate and if so flag it. Just an idea ...
Excel question
Enter this in the data validation formula (Data/Validation/Custom/Formula) in cell A1 and copy all the way down.=COUNTIF(A:A,A1)=1
Excel question
Suppose you want to validate column AAdd to A1 the validation:Allow: CustomFormula: =countif(A:A,A1)<=1Click OKNow select the whole column A, go to Data -> Validation and click "Yes" when asked if you want to expand validation to all the cells in the range.