Sorting by RESULTS (not Formula) - pulldown custom sort, highlighting

[chloec]

Hi There, I am trying to sort a spreadsheet by FORMULA RESULTS. Whenever I invoke the sort feature, the items do not line up properly, I think this is because it sorts the formula. My ultimate goal is to create a sortable list with different columns with the highest 5 values highlighted red and the lowest 5 values highlighted green (values are he result of FORMULAS).


So for example,

user should be able to select a pull-down menu that has the 5 options listed:

2009 Fail %, 2010 Fail %, 2011 Fail %, 2012 Fail %, Rolling 12


Example - If user selects 2010 Fail % - the sort order goes like this:

Quentin, Ila, Nico, Lilly, Paul - highlighted red (top 5 highest values)

Risa, Maura, Jack, Kieran, Opie - highlighted green (top 5 lowest values)


OR the user could select 2011 Fail % and the whole sort order would change...


Here's an example with 10 lines of data:

Partner 2009 Fail % 2010 Fail % 2011 Fail % 2012 Fail % Rolling 12

Ila 50.0% 16.7% 7.7% 0% 0%

Jack 0% 0% 5.0% 0% 0%

Kieran 0% 0% 2.3% 0% 0%

Lilly 20.0% 13.3% 22.2% 40.0% 35.7%

Maura 30.8% 1.2% 0% 6.5% 5.3%

Nico 3.8% 16.7% 3.6% 4.7% 3.7%

Opie 13.3% 0% 4.5% 4.8% 3.3%

Paul 2.4% 11.8% 9.9% 2.8% 3.4%

Quentin 12.5% 23.8% 3.2% 1.6% 1.4%

Risa 8.3% 2.9% 2.5% 2.5% 2.2%


I have a spreadsheet created with the formulas, not sure if that matters - - please advise if you want me to email it to you.


Here's an example of the formula used in ILA's first datpoint (50%):

=IF(COUNTIFS(DateSupplier,$A12,Yr_Formula,LEFT([LXScoreCard_UpdatedWeekly_MGADDS.xls]Sheet1!$B$3,4),Date_Status,"Fail")=0,"0%",COUNTIFS(DateSupplier,$A12,Yr_Formula,LEFT([LXScoreCard_UpdatedWeekly_MGADDS.xls]Sheet1!$B$3,4),Date_Status,"Fail")/(COUNTIFS(DateSupplier,$A12,Yr_Formula,LEFT([LXScoreCard_UpdatedWeekly_MGADDS.xls]Sheet1!$B$3,4),Date_Status,"Pass")+COUNTIFS(DateSupplier,$A12,Yr_Formula,LEFT([LXScoreCard_UpdatedWeekly_MGADDS.xls]Sheet1!$B$3,4),Date_Status,"Fail")))


Thanks for the help. If there's any way that you can point me in the right direction I would really appreciate it. I have looked through the dashboard help pages and get very confused.

 

[SirJB7]

Hi, chloec!

Excel sort feature works on values or formula results, not on formula definitions, if this was your first line implicit question.

Consider uploading a sample file as stated at the second green sticky post at this forums main page.

Regards!

 

[NARAYANK991]

Hi Chloe ,


Do you think this will help ?


http://speedy.sh/YNuDG/Chloe-1.xlsx


All you need to do is select the column you want to sort on , and select Sort Largest to Smallest in the auto-filter drop-down.


Narayan

 

[chloec]

Hi There,

Thanks for the response. Sir JB7, understood what you are saying, but how can I get around - I need excel to sort on results, not on formula. I understand that there's ways of creating helper formulas, but can't figure out how to do this.


Narayan -

Thank you, but that does not help. I uploaded an example spreadsheet for you to review. The problem is that I need to sort by a COLUMN first, then highlight the entire row based on highest/lowest values.


I uploaded the requested spreadsheet. Thank you so much!!

http://speedy.sh/CnQab/chandoo-help.2.xlsx

 

[SirJB7]

Hi, chloec!


Maybe I didn't express myself clearly. Excel always sort by results, that's to say by the values you see displayed in cells, no matter if they're constants or formula results. But it never sort by other thing than results... so I don't get what's your point about it.


Besides this, checking your uploaded file I neither see any macros sorting by different columns nor worksheets or ranges with sort specifications... so would you please elaborate a bit more on what do you want to do when you select an option from H1:K1 on worksheet Formulas?


Regards!

 

[chloec]

Hi SirJB7,

Thank you for the reply. I don't understand why my values aren't sorting correctly. Can you please help me understand? For example - open the spreadsheet from yesterday. Go to Tab 2 "Formulas" and try to auto-filter and sort using cell B3 (2009 Fail %). The numbers that are 0% aren't sorted in order (smallest to largest or vice versa). Zero values seems to distort the sort.


In regards to the spreadsheet.

Tab 1 is the data

tab 2 is the formulas with the results. My preference is to sort using data in H1:K1. When an item is selected the 3 highest and 3 lowest values are shaded and put in order (as shown on tab 3).

tab 3 are screenshots of my proposed sorting preference

Tab 5 is my custom filter sort list


Does this help at all? Is this even posible? Thank you for the help!

 

[SirJB7]

Hi, chloec!


Sorry to say... but:


- there are no macros as it's an .xlsx file, so no sort procedures

- there are no worksheets sorts defined

- there are no conditional formats in any worksheet so as to shaded any highest/lowest values

- the cell selected for "sorting" (false) H1:K1 isn't used in worksheet formulas


I'm afraid that we're talking about different things. Maybe your workbook behaves different in your environment, but I don't think so; despite of this maybe you can attach printscreens of Formula worksheet where it shows what you stated in Sort Examples sheet.


Regards!

 

[chloec]

Hi Sir JB7 and Narayan,

Thank you for your patience.

Can you please confirm you can see 4 tabs in this worksheet?

http://speedy.sh/CnQab/chandoo-help.2.xlsx


In regards to your comments (thank you,again):

- there are no macros as it's an .xlsx file, so no sort procedures

Yes - I don't have macros. I don't use macros and not sure why I would use them for sort procedures. I am looking for help with sorting using the pull-down menu AND conditional formatting the results.


- there are no worksheets sorts defined

I don't know what this means. I did not define a worksheet sort. I guess I could have used the auto-filter. I thought you'd be able to use that to see how ineffective the sort feature is (all zeros end up in the wrong place on sort).


- there are no conditional formats in any worksheet so as to shaded any highest/lowest values

Correct. I have not used any conditional formatting rules. I did, however, take a picture of how I would like conditional formatting to work (tab 3, named "sort examples"). You cannot see the screenshots??


- the cell selected for "sorting" (false) H1:K1 isn't used in worksheet formulas

Correct. H1:K1 was put there so you could see that i wanted a pulldown menu to engage the sort+conditional format features I was looking to learn how to do.


Maybe this is too complicated or I am not communicating well enough? I am sorry if that is the case. I guess if neither you nor Narayan understand what I am trying to do here - the problem lies with me!


Anways, thank you very much for trying!

 

[SirJB7]

Hi, chloec!


Please check if I understood now:


a) you have a workbook with 4 worksheets (see my uploaded printscreens)

b) the problem is related only to 2nd. worksheet Fomulas

c) you haven't placed any macros nor defined any sort condition for this worksheet (from the Data tab, Sort and Filter group, Sort icon)

d) you talk about filter or sort criteria in cells H1:K1 but you don't use it at any place

e) you want to get the data sorted by the related column to the value of H1:K1 cell selection

f) you want to get the data conditionally formatted as you manually did in worksheet Sort Examples

g) you don't mind if VBA code or macros are used to achieve the goal


Link to uploaded file:

https://dl.dropbox.com/u/60558749/Sorting%20by%20RESULTS%20%28not%20Formula%29%20-%20pulldown%20custom%20sort%2C%20highlighting%20-%204%20%28for%20chloec%20at%20chandoo.org%29.rar


Please confirm or rectify these asserts. Thank you.


Regards!

 

[chloec]

Hi There,

You are correct. Thanks. I am excited to see the outcome!


I did notice that I made a "conditional formatting" mistake with tab 3 "sort examples". In example 1 I accidently colored red the wrong set of 3 numbers. I should have highlighted those numbers marked as 12.1% (not 15.6, 9.7 and 6.5%) I apologize for that.


Thanks again!