Not allow a number repeated

Posted on 2011-10-06
Last Modified: 2012-05-12
We have an order form that we use for our clients, and their billing requires a unique VON number for each Manufacturer order.  The form is constantly changing, and I have used Named Ranges such as "VON_Almark", "VON_DavdisonsEggs", ETC.   What is the "softest code possible" that will look at all VON cells and make sure they do the following:

1) Has to be 10 digits
2) Only has to have a VON if the Cost is completed (named ranges of COST_ManufacturerName)
3) Cannot reuse a VON number on the form.

Thanks! Form Screenshot
Question by:sharpapproach
    LVL 29

    Expert Comment

    can you post a sample worksheet so we can cater a solution for you as several elements that need to be concidered
    1) do you have several worksheets in the same workbook to verify ?
    2) you mention named range "VON_Almark", "VON_DavdisonsEggs", ETC. How to look for ETC if we dont have the names makes it a bit more complex !
    3) Presume the VON number you are refering to is in the yellow cell but the picture fail to give us the column is it B?

    ETC ...

    Author Comment

    Here is my test form.  Thanks!
    LVL 29

    Accepted Solution

    Hi again
    Is this what your looking for ?
    Pls try this file and enable macros and see the cursor now in B69 it has a similar value elswhere try changing it and see the result look for the couple of row before this one oyu will see the value change this one to be same as the new one below and see results is this what your looking for ???

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now