dropdown list in alphabetical order data validation...

albertc30

Well-known Member
Joined
May 7, 2012
Messages
1,091
Office Version
  1. 2019
Platform
  1. Windows
Hi all, again...

I have been reading on this and have found a few good youtube videos.

One in particular mentioned 4 ways this could be achieved, the best way being the new function SORT.

Sadly, this is not available on my newly bought office 2019 professional. Just another way Microsoft has to push us in the direction they want us to go.

Another way to do it was by means of a query. To be honest I never have worked with queries and the one thing about it was that it needed refreshing manually?!

There's also, off course the macro. To use this I'd have to copy the column in question to another sheet as to have it sorted independently and only the data I'm interested in.

Is there a better simpler way to achieve this?

Any help is truly appreciated.

Thank you.
Cheers
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
What are you actually trying to do ?
A bit more info about the problem may help, not what you think might be the solution ?
 
Last edited:
Upvote 0
There is no innate way that Excel can take an unordered list on a worksheet and cause it to suddenly appear ordered in a data validation drop-down. So whether you were using the (unavailable) SORT function, query, macro or manually copying & sorting or anything else you would need to have the sorted list stored somewhere.

Is there a better simpler way? Well, assuming no duplicates are to be included in the list you could try this standard formula, copied down. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

This sorted list could be in a hidden column or on another sheet if you want and used for your DV list. If you were to subsequently change cat for zebra the sorted list would automatically update.

Excel Workbook
ABC
1dogcat
2catdog
3mousehorse
4horsemouse
Alphabetical


If you might be adding to or deleting from the list then you could use an Excel table (Insert ribbon tab -> Table -> My table has headers), as I have done below. Again, the formula in the Sorted column should be array-entered & it will fill down the column. Then in the Data validation cell(s) use the DV formula shown. The DV list will automatically expand or shrink as you do the same to the table.

Excel Workbook
HIJKL
1NameDataSorted
2dogData 1cathorse
3catData 2dog
4mouseData 3horse
5horseData 4mouse
6
Alphabetical
#VALUE!
 
Last edited:
Upvote 0
Can you please try my Macro
Code:
Option Explicit
Sub Salim_Data_Val()
Dim D As Worksheet
Set D = Sheets("Sheet1") 'Put here your sheet's name
Dim i#: i = 2
Dim arr
Dim Laste_row#
Laste_row = D.Cells(Rows.Count, "A").End(3).Row
Dim rg As Object
Set rg = CreateObject("System.Collections.Arraylist")
With rg
 Do Until i > Laste_row
  If Not .Contains(UCase(D.Range("A" & i).Value)) _
   And D.Range("A" & i) <> vbNullString Then _
  .Add UCase(D.Range("A" & i).Value)
 i = i + 1
 Loop
 .Sort
 arr = Join(.Toarray, ",")
 End With
 
 With D.Range("C1").Validation
 .Delete
 .Add xlValidateList, Formula1:=arr
  End With
 Set rg = Nothing: Set D = Nothing

End Sub

ABC
1NameA
2dog
3mouse
4horse
5B
6A
7z
8T
9cat

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
A1Name
B1
C1A
A2dog
B2
C2
A3mouse
B3
C3
A4horse
B4
C4
A5B
B5
C5
A6A
B6
C6
A7z
B7
C7
A8T
B8
C8
A9cat
B9
C9

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:
Upvote 0
What are you actually trying to do ?
A bit more info about the problem may help, not what you think might be the solution ?

Outch!

But you're right, my apologies, it was very late when I posted the post.

It's basically a sheet where it contains customers and initially I had an array that was being used as data validation to show the customer's list on a cell.

This off course shows the data as it is and not in alphabetical order.

As I do not want to filter the data on the customer's sheet, I will now as a solution, have the customer's names column copied to another sheet (Data) and will have that info there being alphabetically sorted.

Another thing I notice on the cell where it shows the list is that 1)when you click the dropdown the list is very long showing much more than just the customers available in the list, too much just white (no data) and it's wide like twice what it should be.

Many thanks for your time, and the rest of you that have given input.

I shall come back to you all today at some point.

Much appreciated for your time and off course, for helping.

Cheers.
 
Upvote 0
Another thing I notice on the cell where it shows the list is that 1)when you click the dropdown the list is very long showing much more than just the customers available in the list, too much just white (no data) and it's wide like twice what it should be.
Does this comment relate to any of the suggestions made in this thread? If so, which one(s) & how does it relate to those posts?

Otherwise
1. Exactly where is your DV list?
2. How did you derive the list?
3. What is the actual Data Validation set-up in the cell?


@ salim hasan
Can you please look at the options like 'First Cell In Each Column' or 'User Defined Selection' etc when using the HTML Maker so you don't get those long lists of similar formulas or blanks like have been appearing in many of your posts.
 
Upvote 0
Hello,

Among the possibilities, a simple UserForm could help you out ...:)
 
Upvote 0
Does this comment relate to any of the suggestions made in this thread? If so, which one(s) & how does it relate to those posts?

Otherwise
1. Exactly where is your DV list?
2. How did you derive the list?
3. What is the actual Data Validation set-up in the cell?


@ salim hasan
Can you please look at the options like 'First Cell In Each Column' or 'User Defined Selection' etc when using the HTML Maker so you don't get those long lists of similar formulas or blanks like have been appearing in many of your posts.

I wanted to attach a picture of the file from my PC
But I don't know how to do this
please explain
 
Last edited:
Upvote 0
I wanted to attach a picture of the file from my PC
But I don't know how to do this
please explain
You cannot "attach" any files in the forum and you cannot show images stored on your computer. You can upload images to a file-share site or OneDrive, Google Drive etc and share a link. However, it is your spreadsheets samples that I am talking about. In general they are better than images because other people can copy them and paste data into their worksheets to test with. The issue I have is the part in post 4 in this thread that is below the heading "Worksheet Formulas". For a start there are no formulas, so we did not need to see any of that section. It is also a long section, making your post and the thread harder to read & navigate.

Another example is this post of yours. In the 'Worksheet Formulas' section we only needed to see the formula for D2 since the D3:D9 formula are the same (only referring to their own row). When you select the range on your sheet and go to the HTML maker menu, try selecting one of the options I mentioned before.
 
Upvote 0
One example.
Name list derived from formula is in N4:N12. Some cells say N8: N12 are blank. Formula is below for Data Validation.
In the drop down list you will see only names in N4:N7 (Cells having names). All cells are having formula in N4:N12.

=OFFSET($N$4,0,0,COUNTIF($N$4:$N$12,"?*"))
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top