Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Drop down list - unique values only

Posted on 2012-03-23
15
Medium Priority
?
1,214 Views
Last Modified: 2012-03-28
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
0
Comment
Question by:mcnuttlaw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 8

Expert Comment

by:csoussan
ID: 37758866
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
 
LVL 2

Author Comment

by:mcnuttlaw
ID: 37759836
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 42

Expert Comment

by:dlmille
ID: 37760071
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
 
LVL 8

Expert Comment

by:csoussan
ID: 37760863
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
 
LVL 2

Author Comment

by:mcnuttlaw
ID: 37761799
csoussan: different worksheet, not workbook.

I have one workbook and the named range is in another worksheet.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37761803
@mcnuttlaw - curious if you looked at the DynamicDV! article.  


Dave
0
 
LVL 2

Author Comment

by:mcnuttlaw
ID: 37761898
Yup - playing with it now.
0
 
LVL 8

Expert Comment

by:csoussan
ID: 37762056
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
 
LVL 2

Author Comment

by:mcnuttlaw
ID: 37765861
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
 
LVL 8

Accepted Solution

by:
csoussan earned 1500 total points
ID: 37767372
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
 
LVL 2

Author Comment

by:mcnuttlaw
ID: 37769602
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
 
LVL 42

Expert Comment

by:dlmille
ID: 37769604
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
 
LVL 2

Author Comment

by:mcnuttlaw
ID: 37769667
0
 
LVL 2

Author Closing Comment

by:mcnuttlaw
ID: 37779717
Although it wasn't exactly what I was looking for, I'll grade an A since the expert was very helpful.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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.

636 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