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
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
Some reading
https://www.myonlinetraininghub.com/...ic-unique-list
https://www.myonlinetraininghub.com/...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. 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/show...rkbook-Startup
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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?
3. why can't I click on the dropdown list in C2?
I posted in the thread instead of here
Hidden names
Option Explicit Sub unhideAllNames() Dim tempName As Excel.Name For Each tempName In ActiveWorkbook.Names tempName.Visible = True Next End Sub
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Oh okay so they're hidden.. Why am I unable to click on the dropdown list?
Capture.JPG
Using Example_1.xlsm, does the list (above) look the same?
What happens when you try the dropdown list on the other sheet?
Capture.JPG
Last edited by Paul_Hossler; 05-03-2020 at 12:06 PM.
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Oh let me try