Good afternoon. I have a dropdown menu in $D$4 allowing users to select from a list of values $S4:$S20 that pulls values from another sheet. You can see the resulting data in D7:K15. Based on the list item the end user selects in D4, the values in D7:K15 will change.
My goal is to sort all data in the table so it's ranked by column K from highest to lowest and I need a formula that would make that happen. Understand that changing the selection in D4 will change the values in column K likely necessitating the formula to resort.
Values in D7:D15 will remain fairly constant over time but would likely change order based on the results of the formula sorting by the values in column K
Thanks in advance
My goal is to sort all data in the table so it's ranked by column K from highest to lowest and I need a formula that would make that happen. Understand that changing the selection in D4 will change the values in column K likely necessitating the formula to resort.
Values in D7:D15 will remain fairly constant over time but would likely change order based on the results of the formula sorting by the values in column K
Thanks in advance
| MrExcel.xlsx | |||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | K | L | M | N | O | P | Q | R | S | |||||||
| 4 | Brown | ||||||||||||||||||||
| 5 | All Market Total | 5 | Last Mo | Prev Mo | 6 Mo Ave | ||||||||||||||||
| 6 | Banana | 1 | Market Ranking 1 | Total Revenue | |||||||||||||||||
| 7 | Cherry | 1 | Banana | $0 | $0 | $0.00 | ` | Green | |||||||||||||
| 8 | Peach | 1 | Cherry | $1,350 | $1,350 | $1,750.00 | Silver | ||||||||||||||
| 9 | Coconut | 1 | Peach | $0 | $0 | $0.00 | Orange | ||||||||||||||
| 10 | Lime | 1 | Coconut | $0 | $0 | $300.00 | Blue | ||||||||||||||
| 11 | Pineapple | 1 | Lime | $0 | $0 | $0.00 | Red | ||||||||||||||
| 12 | Strawberry | 1 | Pineapple | $1,200 | $1,200 | $1,600.00 | Magenta | ||||||||||||||
| 13 | Watermelon | 1 | Strawberry | $0 | $0 | $0.00 | Purple | ||||||||||||||
| 14 | Lemon | 1 | Watermelon | $0 | $0 | $0.00 | White | ||||||||||||||
| 15 | Lemon | $0 | $0 | $0.00 | Yellow | ||||||||||||||||
| 16 | $0.00 | Violet | |||||||||||||||||||
| 17 | $0.00 | Brown | |||||||||||||||||||
| 18 | $0.00 | Chocolate | |||||||||||||||||||
| 19 | Black | ||||||||||||||||||||
| 20 | Silver | ||||||||||||||||||||
NeedsHelp | |||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E7:E18 | E7 | =IFNA(INDEX('Revenue Analysis'!P$2:P$161,MATCH($D7&$D$4,INDEX('Revenue Analysis'!$B$2:$B$161&'Revenue Analysis'!$C$2:$C$161,),0)),"") |
| F7:F18 | F7 | =IFNA(INDEX('Revenue Analysis'!O$2:O$161,MATCH($D7&$D$4,INDEX('Revenue Analysis'!$B$2:$B$161&'Revenue Analysis'!$C$2:$C$161,),0)),"") |
| K7:K18 | K7 | =SUM(E7:J7)/6 |
| B10:B14,B6:B8 | B6 | =IFERROR(VLOOKUP($A6,Recap!$A:$B,2,FALSE),"-") |
| N9 | N9 | =IFERROR(INDEX($A$2:$A$4,AGGREGATE(15,6,(ROW($A$2:$A$4)-ROW($A$2)+1)/($B$2:$B$4=COLUMNS($E$2:N$2)),ROWS(N$2:N7))),"") |
| D7:D20 | D7 | =IFERROR(INDEX($A$5:$A$82,AGGREGATE(15,6,(ROW($A$5:$A$82)-ROW($A$5)+1)/($B$5:$B$82=COLUMNS($D$7:D$7)),ROWS(D$7:D7))),"") |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| D4 | List | =$S$6:$S$20 |
