Sort dynamic list

[abdulhaque]

=OFFSET($D$2,0,0,COUNTA($D$2:$D$51)-COUNTIF(D2:D51,""),1 this is the formula that creates a dynamic list for a drop down using data from column D. Data in column D is not sorted in alphabetical order. Without having to sort the data in column D, can the list for the drop down using that formula be updated to also sort the data in the drop down?

 

[jasonb75]

Re: Sort dymanic list

You would not be able to sort it in the dropdown, if you don't want to sort the original list then you will need to duplicate it in a different column using formulas.

 

[abdulhaque]

Re: Sort dymanic list

Thanks that can be a suitable workaround, let me have a go.

 

[jasonb75]

Re: Sort dymanic list

The easiest way to do that would be to use 2 columns.

In E2 and fill down (As this is for a dropdown, I've assumed no duplicates).

=IF(D2="","",COUNTIF(D$2:D$51,"<="&D2))

In F2 and fill down

=IFERROR(INDEX(D$2:D$51,MATCH(ROWS(F$2:F2),E$2:E2,0)),"")

Dropdown formula

=OFFSET($F$2,0,0,MAX($E$2:$E$51),1)

 

[abdulhaque]

Re: Sort dymanic list

Thanks, nearly there, it introduced empty lines...

 

[jasonb75]

Re: Sort dymanic list

In that case, try changing the first formula to

=IF(D2="","",COUNTIFS(D$2:D$51,"<>",D$2:D$51,"<="&D2))

 

[abdulhaque]

Re: Sort dymanic list

Made no difference

 

[jasonb75]

Re: Sort dymanic list

Hopefully this one will work

=IF(D2="","",COUNTIFS(D$2:D$51,"*?",D$2:D$51,"<="&D2))

The result should start from 1 in the row for 'Add orderable to catalog'

edit:- results for first 3 rows should be 5,11,1

 

[abdulhaque]

Re: Sort dymanic list

 

[jasonb75]

Re: Sort dymanic list

I think that I need to start today again

Column F formula should be

=IFERROR(INDEX(D$2:D$51,MATCH(ROWS(F$2:F2),E$2:E$51,0)),"")

The dropdown formula looks correct, but given the mistakes found in the other formulas...