You are here:

Excel/data validation list

Advertisement


Question
QUESTION: Hi Tom

I would like to create a data validation list.  The column of names from which the list is drawn contains several duplicate names.  Is their a way of ensuring that the list only takes one example of each name.  E.g the names in the column will look like this:

7SR2
7SR2
7SR2
8ZX1
8ZX1
8ZX1
9CD3
9CD3
9CD3

I would like the data valitaion list to look like this:
7SR2
8ZX1
9CD3

Thanks in advance
Chris Mitchell

ANSWER: Christopher Mitchell,

I believe the only way to do that would be to build that unique list in another location and have the data validation reference that list.  There is no option or setting in any of the dropdown boxes including data validation list option which will only display unique values.

This unique list could be built using a formula or a macro.

Now the thought would be that if a formula can produce a unique list then perhaps it could be placed in a defined name an built "virtually" so to speak.  To the best of my knowledge, data validation is not very robust - it only supports very limited formula use.  So I don't think that is an option.  

I believe it was in Excel 2010 that data validation was modified to accept off sheet references - so you could perhaps use a scratch sheet to build your unique list.

--
Regards,
Tom Ogilvy



---------- FOLLOW-UP ----------

QUESTION: Hi Tom

Thank you for your solution to my earlier problem.  I have an interesting new issue that arises from your earlier solution.  I have sent a small workbook to you to illustrate it.  The workbook contains three sheets.  Two of them “sheet1” and “sheet2” contain the same information.  The information in “sheet2” is linked directly to the information in “sheet1”

I am able to produce a unique list from the information in column k in “sheet1” but unable to do the same thing from column k in “sheet2”. I am using the “data”, “advanced” commands.

Can you shed some light on the reason for this?

Chris Mitchell

ANSWER: Chris,

I mailed your workbook back.  I was successful and the key was starting the action in the destination sheet.  This was always true in earlier versions of excel when using the advanced filter to copy to another worksheet.  (I also had to select two cells in the destination sheet to avoid the warning message that ended the attempt if I only selected a single cell - I still get a different warning about not being able to determine the data source, but that can be ignored.

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Hi Tom

As ever, thank you for your prompt response.  Could you clarify your solution?  I cannot quite make it work.  I am sure it is something that I am not doing my end.  I have recorded another solution, via the macro record button but would prefer to use the unique list option.

Chris Mitchell
ps here is the text of the recorded macro:

Sub pastevalues2()
'
' pastevalues2 Macro
'

'
   Sheets("Sheet1").Select
   Range("A1:CH673").Select
   Selection.Copy
   Sheets("Sheet2").Select
   Range("A1").Select
   ActiveSheet.Paste Link:=True
   Rows("1:1").EntireRow.AutoFit
   Range("A1").Select
   Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
       SkipBlanks:=False, Transpose:=False
   Range("A1").Select
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   Range("A1").Select
End Sub


Thanks
Chris Mitchell

Answer
Put the header from the source data where you want to paste the unique list (Sheet3).

Select the header and the next cell (two adjacent cells in the same column) in the destination sheet (Sheet3).  (you do need to have the header in the destination cell).

then do
Data=>Advanced.
in the menu

You should get a warning that excel can't determine your source data.  

"Microsoft Office Excel cannot determine which row in your list or selection contains column labels, which are required for this command."  then it give you 4 lines of things you could do.  Just click OK for that dialog

(if you don't get this message, you didn't select two cells with the top cell

The advanced filter dialog should now be visible.

Click on the Unqiue check box
Click on copy to another location so that radio button is selected
now the destination box is active.  Click in that and select the single cell in the destination sheet were you want the data (go from two cells selected to 1) so that address appears in the copy to box.

select the List Range: (click in the box)

now navigate to the you source data and select the header and the data below (sheet2!K1:K21)

when you do that, then the address Sheet2!K1:K21 should appear in the list range: box

Now click OK.


Tested in Excel 2007 (so I guess that is where they made the change about filtering to a separate sheet) but would expect no difference in Excel 2010.

Hope that is clearer.   Tested it over and over and it worked.

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.