Solved

Drop down list - unique values only

Posted on 2012-03-23
15
447 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
ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

770 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