View Full Version : Duplicate Values in Data Validation
jazz2409
05-01-2020, 07:16 PM
Hello, how do I remove the duplicates in the data validation on column B?
I read about =SORT(UNIQUE for O365 however that's not showing up for me. (Maybe because I am using O365 Plus Pro?)
Attached is the sample file.
thank you
Paul_Hossler
05-01-2020, 11:34 PM
Some reading
https://www.myonlinetraininghub.com/excel-sorted-dynamic-unique-list
https://www.myonlinetraininghub.com/excel-dynamic-arrays
Especially
Data Validation
Now that we have our nicely sorted unique list it makes sense that you might want to use it in a data validation list (https://www.myonlinetraininghub.com/excel-drop-down-lists). To reference a spilled array we simply use the new Spilled Range Operator, #, as shown in the Data Validation ‘Source’ field below:
The above approach requires a 'sort of' 'helper' column
A more involved approach that doesn't require the helper column
http://www.vbaexpress.com/forum/showthread.php?67209-Creating-dynamic-List-on-Workbook-Startup
jazz2409
05-02-2020, 12:57 AM
Hi Paul, I have a few questions:
1: what are _xlfn and _xlws? Are they named ranges? Why can't I find them in the Name Manager?
2. how come sort and unique are working with those two but I can't make it work? :rotlaugh:
3. why can't I click on the dropdown list in C2?
I posted in the thread instead of here :rotlaugh::rotlaugh:
jazz2409
05-02-2020, 01:03 AM
A more involved approach that doesn't require the helper column
http://www.vbaexpress.com/forum/showthread.php?67209-Creating-dynamic-List-on-Workbook-Startup
Hmmm I'm not sure how I'll be able to apply this to mine because I need to use it in multiple rows in the same column :(
Paul_Hossler
05-02-2020, 07:19 AM
Hidden names
Option Explicit
Sub unhideAllNames()
Dim tempName As Excel.Name
For Each tempName In ActiveWorkbook.Names
tempName.Visible = True
Next
End Sub
jazz2409
05-02-2020, 08:21 PM
Oh okay so they're hidden.. Why am I unable to click on the dropdown list?
Paul_Hossler
05-03-2020, 11:37 AM
26562
Using Example_1.xlsm, does the list (above) look the same?
What happens when you try the dropdown list on the other sheet?
26564
jazz2409
05-05-2020, 06:51 PM
Oh let me try
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.