dropdown list in alphabetical order data validation...

albertc30

Well-known Member
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
 

Michael M

Well-known Member
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:

Peter_SSs

MrExcel MVP, Moderator
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
1dog
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 1 horse
3catData 2dog
4mouseData 3horse
5horseData 4mouse
6
Alphabetical
Data Validation
L2List
=INDIRECT("Table1")
 
Last edited:

salim hasan

Board Regular
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:

albertc30

Well-known Member
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.
 

Peter_SSs

MrExcel MVP, Moderator
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.
 

James006

Well-known Member
Hello,

Among the possibilities, a simple UserForm could help you out ...:)
 

salim hasan

Board Regular
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:

Peter_SSs

MrExcel MVP, Moderator
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.
 

kvsrinivasamurthy

Well-known Member
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,"?*"))
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top