• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA/Formula: Prevent Duplicate Entry in a Table column [SOLVED]

inddon

Member
Hello There,

I have a column (Invoice Number) in table. It has a Data Validation (DV) to prevent duplicate entry of Invoice Numbers as below:
=COUNTIF($A$2:$A$4,A2)=1

When I extend the table, this entry doee not work in the newly extended column of Invoice Number.

Is there a new way to write this formula in a Table column via Data Validation as well as in VBA? Attached a sample workbook

Could you please advise?


Thanks & regards
Don
 

Attachments

  • Duplicate Invoice Numbers.xlsm
    9.5 KB · Views: 7
Hello Narayan,

Thank you for your reply.

It works, but when I extend the table then it does not work for the extended cells.

Could you please try to check the above scenario?

Regards
Don
 
Hi Don ,

It works for me. If I add a new row by pressing the TAB key , and enter a number which is a duplicate , the DV error message pops up.

Check the following Excel option :

Extend data range formats and formulas

Narayan
 
Hi Don ,

It works for me. If I add a new row by pressing the TAB key , and enter a number which is a duplicate , the DV error message pops up.

Check the following Excel option :

Extend data range formats and formulas

Narayan


Hello Narayan,

The method you showed by extending the table by a tab key, the DV works perfect.

When you extend the table via the left mouse, the DV gets copied , but it does not gets fired.


I am attaching a jpeg file as well as the workbook for your reference.


Looking forward to hearing from you

Regards
Don
 

Attachments

  • Duplicate Invoice Numbers-1.xlsm
    9 KB · Views: 4
  • Duplicate Invoice Numbers Case.jpg
    Duplicate Invoice Numbers Case.jpg
    114.3 KB · Views: 4
Hi Don ,

You are right that the DV rules are present but don't work the way they are supposed to.

I can only conclude that this is a bug ; I have seen it in Excel 2010 , let me see if it is present in Excel 2007 also.

In the meantime , let us see if Googling this throws up anything.

Narayan
 
Hi Don ,

You are right that the DV rules are present but don't work the way they are supposed to.

I can only conclude that this is a bug ; I have seen it in Excel 2010 , let me see if it is present in Excel 2007 also.

In the meantime , let us see if Googling this throws up anything.

Narayan

Hello Narayan,

We came across a bug :) If there is a workaround great.

How can this be achieved via VBA? Could you please assists?


Thanks & regards
Don
 
Hi Don ,

See this file. If it works reliably , you can eliminate the Data Validation.

Narayan
 

Attachments

  • Duplicate Invoice Numbers-1.xlsm
    14.8 KB · Views: 10
Hi Don ,

See this file. If it works reliably , you can eliminate the Data Validation.

Narayan


Hello Narayan,

This works perfect. I will stick to your code. Thank you for your help.

Just another question in regards to duplication. If I would like to add additional columns for check (eg. Receipt Number, Order Number), how could this be incorporated in your VBA code?

Regards
Don
 
Hello Narayan,

I have included the other columns in the elseif statement. Hope that is the right way to do!

How to control that this code only fires when the cursor is on the Invoice Number. Now it will fire for all the columns in the table?

Thanks & look forward for your reply


Regards
Don
 
Hi Don ,

Can you check back after some time ?

Basically the existing code needs to be repeated , once for each column that you wish to validate.

Since the existing column has a named range assigned to it , for every added column , an additional named range can be created.

Narayan
 
Hello Narayan,

Thank you for your reply. I have defined the named range for the rest of the columns.

Sure, I will check back later and wait for your reply.

Regards
Don
 
Back
Top