Define Name Range: Formula to sort the range ascending wise

[inddon]

Hello There,

I have a table and have defined a named range on a column.
In another table I am using this defined named range as a data validation.

I would like when the user clicks on the drop down list, the list is sorted ascending wise.
Attached a sample workbook for your reference.

Could you please advice how this can be achieved?

Thanks & regards,
Don

 

[prasaddn]

hi,

does an VBA solution work for you? or looking for only formulae based solution?

Regards,
Prasad DN

 

[bosco_yip]

A formula with helper solution.

1] Added helper Column H, in H2 formula copied down :

=INDEX([Full Name],MATCH(ROWS($1:1)-1,INDEX(COUNTIF([Full Name],"<"&[Full Name]&""),0),0))

2] Name Manager >> select "Names" >> click "Edit" >> Change "Refer to" formula to :

=Table1[Sorted namelist]

>> OK

3] See attachment

Regards
Bosco

 

[inddon]

hi,

does an VBA solution work for you? or looking for only formulae based solution?

Regards,
Prasad DN

Hi Prasad,

Thank you for your reply. I would prefer to have the formula listed in the Named Range. However, it is good to know how this can be done using VBA as well.

Yes, please let me know how this can be done using VBA.


Thanks & regards,
Don

 

[inddon]

A formula with helper solution.

1] Added helper Column H, in H2 formula copied down :

=INDEX([Full Name],MATCH(ROWS($1:1)-1,INDEX(COUNTIF([Full Name],"<"&[Full Name]&""),0),0))

2] Name Manager >> select "Names" >> click "Edit" >> Change "Refer to" formula to :

=Table1[Sorted namelist]

>> OK

3] See attachment

Regards
Bosco

Hi Bosco,

Thank you for your solution. Can this be done without using the helper column in the Named Range itself?

Regards,
Don

 

[bosco_yip]

Hi Bosco,

Thank you for your solution. Can this be done without using the helper column in the Named Range itself?

Regards,
Don

In the "Full Name" column header >> click the dropdown menu >> select "Sort A to Z",

but your Table order will be all changed in accordingly.

Regards
Bosco

 

[inddon]

Hi Bosco,

Thank you for your reply. That option would not be possible. Any other working you can think of?

Regards,
Don