Drop down list - unique values only

I have two worksheets:

Sheet1 - dynamic, one column of names (starts at A3, ends ???)
Sheet2 - would like to place a drop down list in B5 of unique names in alphabetical order.

Excel 2010
vba is okay
prefer no addins
LVL 2
mcnuttlawAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

csoussanCommented:
I like the suggestion above as I generally favor using built in Excel features and functions over code.  However, if you're looking for a code solution, try the following:

1) Highlight the range that contains your drop-down list. If you have a header row, include the header.
2) Click Insert --> Table --> OK (Make sure the box is checked for "My Table Has Headers" if applicable).
Insert Table3) Click on the Table Tools tab, highlight the table name and rename it to DropDown.
Rename Table4) Hightlight range that contains your drop-down list.  DO NOT include the header row.
5) Name the range DropDownList. (Formula --> Define Names --> Name: DropDownList --> OK)
Define Name6) Change the Data Validation Source to =DropDownList
Data Validation7) Right-click on the worksheet tab at the bottom of the window, select View Code and paste the following code into the code window.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("DropDown")) Is Nothing Then
    ActiveSheet.Range("DropDown[#All]").RemoveDuplicates Columns:=1, _
        Header:=xlYes
    Range("DropDown").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End If
End Sub

The code is designed to automatically remove duplicates and sort the list anytime you do anything within the named range "DropDown".  The named range "DropDown" will automatically extend or shrink when you add anything to cells adjacent to the table (built-in feature of tables).
0
mcnuttlawAuthor Commented:
I have a named (dynamic) range that is in another worksheet that can be used to populate the Data Validation list.

Possible with your code?
0
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

dlmilleCommented:
This will work on ANY data validation list you have that has a list range associated with it, it provides sorted, unique lists, auto-complete, and data validation features, etc.

------------------------------
You should find this article useful (if so, a YES vote would be appreciated). At the bottom, is a utility for DynamicDV! which provides sorted, unique lists for any data validation list.

Try it out.  It is an add-in, however, I can help you integrate it into your workbook or Personal.XLSB so you don't have to worry about having addins at the end of the day.

Here's the article:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_6429-Part-II-Drop-Down-List-with-Unique-Distinct-Values-ComboBox-ListBox-and-Data-Validation-List-Bonus.html

Attachment II v2. is the download you want for the initial test.

To integrate the utility (after you're satisfied it does what you want), it would only require the copy of a couple of the modules into your workbook, if that's really necessary, to eliminate the "add-in" effect.

Cheers,

Dave
0
csoussanCommented:
The only way to use a list referring to a named range from another workbook is to have both workbooks open at the same time in the same instance of Excel.  You will need a named range in both workbooks.  In the workbook that will have the Data Validation list, when defining the range name, make sure you select Workbook from the Scope drop down box.  Also, in the Refers to box, you'll need to type a reference to the named range in the source workbook:

*Type an equal sign, then the source workbook name and extension
*Next, type an exclamation mark, followed by the range name in the source workbook.
*For example, the completed reference would look something like this: =OtherWb.xlsx!DropDownList

The Contextures.com web site has a great write-up with step-by-step instructions if needed (http://blog.contextures.com/archives/2009/05/12/data-validation-list-from-different-workbook/).  

Hope this helps!
0
mcnuttlawAuthor Commented:
csoussan: different worksheet, not workbook.

I have one workbook and the named range is in another worksheet.
0
dlmilleCommented:
@mcnuttlaw - curious if you looked at the DynamicDV! article.  


Dave
0
mcnuttlawAuthor Commented:
Yup - playing with it now.
0
csoussanCommented:
Sorry mcnuttlaw, I misread your post.  If it's all in the same workbook you can follow the steps I originally listed except you will need to make sure you select Workbook from the Scope drop down box.
0
mcnuttlawAuthor Commented:
Sorry but I'm having difficulty implementing the code.

I have a named range called Players in Sheet1.

I put the code into Sheet2 and created a DV with '=Players' as the Source.

From there, I don't know how to get the code to execute and don't see where the 'Scope' drop down is located.
0
csoussanCommented:
The code has to be in the same sheet as the table. I've attached a sample spreadsheet.  Sheet1 has a table named Player and a named range of PlayerList (everything in the table except the header row).  Sheet2 has the Data Validation with Source: =PlayerList. Let me know if you have any questions.
Remove-Duplicates-From-DropDown-.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mcnuttlawAuthor Commented:
I would prefer not to have to create a table (for less user confusion) and would like to rely solely on a named range.

dlmille's article gets me what I want but I want to see if this method can work.
0
dlmilleCommented:
If you enjoyed the article or found it helpful, a YES vote is appreciated.  Anything I can do to help you get the utility up and running, just holler.

Dave
0
mcnuttlawAuthor Commented:
0
mcnuttlawAuthor Commented:
Although it wasn't exactly what I was looking for, I'll grade an A since the expert was very helpful.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.