Excel/data validation list
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:
I would like the data valitaion list to look like this:
Thanks in advance
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.
[an error occurred while processing this directive]---------- 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?
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.
---------- 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.
ps here is the text of the recorded macro:
' pastevalues2 Macro
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
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).
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.