Sort data validation list dynamically

[access_mk]

Hi,


I am looking for an easy way to sort a named list (which is populated from a series of column headers say from a table of data). I need to put those items in another cell as a data validation list (sorted) using a formula or anything possibly without a macro.. Please help !!!


e.g. Sheet1 :


imagine: Books Notebooks Computers Tablets Smartphones Phones.. etc as column headers of a table of data


so, I got a named range : myCategories = Sheet1!$A$1:$Z$1


Sheet2 :


Say in A1 : If I use myCategories as it is in data validation, then I will get a list without any order as they appear in Sheet1. But I want the list to be displayed in ascending order.


Please help !! with or without a macro !!!

 

[NARAYANK991]

Hi ,


Let us assume your raw data is in the range named Data_Range , and refers to :


=Sheet2!$A$2:$K$2


Let us assume your sorted data is in the range named Sorted_Range , and refers to :


=Sheet3!$B$2:$N$2


This range will consist of the data items in Data_Range in sorted order , and if the range extends beyond the number of items , the additional cells will consist of #NUM! values.


Create a third named range Validation_List , referring to :


=OFFSET(Sorted_Range,0,0,1,COUNTA(Sorted_Range)-SUM(IF(ISERROR(Sorted_Range),1,0)))


This will restrict the validation list to only those data items which are not #NUM!


Use this range for your drop-down.


The formula to be used for the range Sheet3!$B$2:$N$2 , to generate the sorted values is :


=INDEX(Data_Range, MATCH(SMALL(COUNTIF(Data_Range, "<"&Data_Range), COLUMN(A1)), COUNTIF(Data_Range, "<"&Data_Range), 0))


entered as an array formula ( using CTRL SHIFT ENTER ) ; copy this formula across to as many cells as you want ; after populating the cells with valid data items , the remaining cells will consist of #NUM! values.


Narayan

 

[access_mk]

Hi Narayan,


First of all thank you for attending my request


I would like to add some more detail here.

I have only a table of data which is NOT SORTED (because user may add items dynamically column-wise or row-wise), and I don't have any more named ranges like you mentioned "Sorted_Range" or "Validation_List". I would like to add one data validation list based on this data only in another sheet cell. You can refer my e.g. above for reference.


Sorry, If I couldn't get your reply correctly. Could you please explain more if I got it wrong.


-access_mk

 

[NARAYANK991]

Hi ,


What I understood was that you have column headers in the first row ; in my example , these are in the range named Data_Range.


I also understood that elsewhere in your workbook , you want to have a drop-down , which will use the items from these column headers ; but since these headers are not in sorted order , you want them to be sorted before they can be used in the drop-down validation list.


In my example , I have used the range Sorted_Range to refer to the range of column headers , but in sorted order. To get the data from the original range of Sheet2!$A$2:$K$2 to the range Sheet3!$B$2:$N$2 in sorted order , the cells Sheet3!$B$2:$N$2 need to have the following formulae in them :


=INDEX(Data_Range, MATCH(SMALL(COUNTIF(Data_Range, "<"&Data_Range), COLUMN(A1)), COUNTIF(Data_Range, "<"&Data_Range), 0))


This formula is to be entered in Sheet3!$B$2 as an array formula ( using CTRL SHIFT ENTER ) , and copied across as far as you want ; if you stop at the right column , the cells will all contain valid data from the original range Sheet2!$A$2:$K$2 , but if you go beyond this point , the remaining cells will all contain #NUM! values.


If you now want to use this range of sorted values in your data validation list , you can exclude the #NUM! values by defining a third named range Validation_List referring to the formula :


=OFFSET(Sorted_Range,0,0,1,COUNTA(Sorted_Range)-SUM(IF(ISERROR(Sorted_Range),1,0)))


Nowhere in this entire exercise does your data itself play any part ; we are talking only about the column headers being used in a drop-down validation list , in sorted order.


Narayan

 

[Luke M]

Access_mk,

Narayan's solution requires you to create the additional named ranges. If we're going to use some formulas to sort the list, this requires additional cells that will contain the formulas. Hence, the Sorted_Range. Also, since the data validation is being used on another sheet, you have to use named ranges, or XL won't let you do it.


So, to restate Narayan's idea:

1. Define the unsorted list as Data_Range

2. Setup some cells with this array formula:

=INDEX(Data_Range, MATCH(SMALL(COUNTIF(Data_Range, "<"&Data_Range), COLUMN(A1)), COUNTIF(Data_Range, "<"&Data_Range), 0))


3. Name the range of cells with formula as Sorted_Range.

4. Create a new Named Range named Validation_List, defined via this formula:

=OFFSET(Sorted_Range,0,0,1,COUNTA(Sorted_Range)-SUM(IF(ISERROR(Sorted_Range),1,0)))


5. On Sheet 2, in the cell you desire, set the Data Validation, List:

=Validation_List


EDIT: Narayan, looks like you beat me to it this time! =)