Formula Based Cell Sort with conditional formatting and data validation

I have a project sheet that I am wanting to use to arrange projects by priority. My 5 priorities are "N/A, Done, Low, Med., High". These priorities are available in a drop down list and show up color coded when selected. That part...I've got. I want to be able to sort without using a custom sort. Using a custom sort would require everyone else to learn to use custom sort...and that is more difficult than anything excel has to offer. Lol.

I want to sort A-Z or Z-A and have these values fall in by priority. The way I would imagine it to work is that "N/A" = 1, "Done" = 2, "Low" = 3, "Med." = 4, "High" = 5

The question would be: How do I make my sheet sort by #'s 1-5 but show "N/A, Done, Low, Med., High"?

How about...

Use this in another column to apply the values and then sort by that column

=IF(A1<>"",LOOKUP(A1,{"Done","High","Low","Med.","N/A"},{2,5,3,4,1}),"")

The only other thing that come to mind, create a custom list. This list would contain your sort order; therefore, nobody has to remember the order for the sort, it is built into the custom list.

http://office.microsoft.com/en-us/ex...010222142.aspx

Could this be a option?

You could even build this into a little worksheet change event where the user enters a value, "Done", "Low", "High", etc., and the sheet will auto sort based on your custom list.

I was going to suggest that at first as 1 of 2 options, but because you explictiy stated to stay away from sort, I gave in to the pressure

You're very welcome...glad you got it all sorted out and thanks for the feedback

If this satisfies your query, please don't forget to mark the thread as solved.