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 |