+ Reply to Thread
Results 1 to 6 of 6

Formula Based Cell Sort with conditional formatting and data validation

  1. #1
    Registered User
    Join Date
    05-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2010
    Posts
    7

    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"?
    Last edited by DAC_LCS; 05-25-2012 at 02:11 PM.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,064

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

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

  3. #3
    Registered User
    Join Date
    05-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2010
    Posts
    7

    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. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,064

    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. #5
    Registered User
    Join Date
    05-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2010
    Posts
    7

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

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

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,064

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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