Solved

Drop down list - unique values only

Posted on 2012-03-23
15
433 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
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 37

Expert Comment

by:Neil Russell
ID: 37756674
0
 
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
 
LVL 41

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 41

Expert Comment

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


Dave
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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 500 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 41

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

706 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

20 Experts available now in Live!

Get 1:1 Help Now