# Formula Based Cell Sort with conditional formatting and data validation

1. ## 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"?

2. ## Re: Formula Based Cell Sort with conditional formatting and data validation

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}),"")

3. ## Re: Formula Based Cell Sort with conditional formatting and data validation

Thanks for the formula Jeffrey. I'll give that a shot.

However, doing this would be the same as making another column and saying: =IF(C2="High", 5, IF(C2="Med.", 4....... Right? I'd rather it all be central if possible...1 column instead of 2 for this.

4. ## Re: Formula Based Cell Sort with conditional formatting and data validation

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.

5. ## Re: Formula Based Cell Sort with conditional formatting and data validation

Now we're talking! Thanks Jeff. This should do it

6. ## Re: Formula Based Cell Sort with conditional formatting and data validation

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.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1