Solved: How do I sort a dynamic named range?

[Gingertrees]

Related but somewhat different from this post:
http://www.vbaexpress.com/forum/showthread.php?t=28399

Users can add items in the POCItems sheet from A3:A265. This info is then put into a dynamic named range "GOALS" from C300:C562.

In the blue cells on the Plan of Care and Plan of Care pg.2 sheets, users can select from data validation lists of "GOALS". Due to the sheer number, it would really help if "GOALS" were alphabetized.

Anyone know how to sort the values in a dynamic named range, even though each cell formula is something like
{=IF(ISNUMBER(B300),INDIRECT("A"&B300),"")} ?

[MaximS]

Hey Gingertrees,

I've modified your file a little bit adding UDF which will convert your range into sorted Array without formulas returning blanks.

This will be a base for your named range and validation list.

All works regarding to value changes on your worksheets.

See attached for details.

[bruinenat]

thx

[Gingertrees]

:clap: Thank you so much, MaximS! :cool: That was just what I needed. AND I was able to use the same principle on another sheet to sort another named range of client IDs. Hurrah!!!!

BTW, I think you should submit that to the knowledge base - I think many people will find it useful.