Hello,
I am trying to populate unique names from range F11:F120 to M13:M29 based on whether range B4:B7 >"".
I cannot seem to wrap my head around which formulas to use. I would prefer to have this completed with a formula rather than VBA. This sheet will be used by people that do not have a lot of experience using Excel. My experience is about 6 months of trying to teach myself and understand formulas, so any help would be appreciated.
I am trying to populate unique names from range F11:F120 to M13:M29 based on whether range B4:B7 >"".
I cannot seem to wrap my head around which formulas to use. I would prefer to have this completed with a formula rather than VBA. This sheet will be used by people that do not have a lot of experience using Excel. My experience is about 6 months of trying to teach myself and understand formulas, so any help would be appreciated.
Util Tracker WIP.xlsx | ||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |||
3 | Key 1 | Key 2 | Key 3 | Date | RDC Total Utilization | |||||||||||||||
4 | Cesar | 98.97% | Ivan | Fabian | 4/1/2022 | 51 | ||||||||||||||
5 | Lujan | 96.06% | Ashleigh | Marco | 98.60% | |||||||||||||||
6 | Jessica | 99.83% | Ta | Ana | ||||||||||||||||
7 | Jose G | 99.29% | Eugenia | Alexis | Total Closed | |||||||||||||||
8 | 51 | |||||||||||||||||||
9 | Columns For Closed Trailers | |||||||||||||||||||
10 | Leader | Dock Door | Cube | Weight | Utilization | Loader | Comments | Key 1 | ||||||||||||
11 | Jessica | 194 | 2726 | 40585 | 96.63% | JIM | ||||||||||||||
12 | Jessica | 198 | 2300 | 42147 | 100.35% | JIM | Loader | Avg Utilization | Trailers Closed | Avg Shift Utilization | ||||||||||
13 | Jessica | 175 | 2621 | 42002 | 100.00% | JIM | JIM | 99.46% | 8 | 98.60% | ||||||||||
14 | Jessica | 196 | 2412 | 42083 | 100.20% | JIM | TIM | 98.88% | 7 | |||||||||||
15 | Jessica | 193 | 2655 | 42021 | 100.05% | JIM | JOHN | 98.53% | 11 | |||||||||||
16 | Jessica | 195 | 2908 | 36332 | 96.93% | JIM | MIKE | 97.89% | 12 | |||||||||||
17 | Jessica | 191 | 2328 | 42218 | 100.52% | JIM | KAYLA | 98.63% | 13 | |||||||||||
18 | Jessica | 176 | 3029 | 32287 | 100.97% | JIM | ||||||||||||||
19 | Jessica | 197 | 2837 | 35936 | 94.57% | TIM | ||||||||||||||
20 | Jessica | 188 | 2474 | 38692 | 92.12% | TIM | Trailers Closed | |||||||||||||
21 | Jessica | 180 | 3075 | 40147 | 102.50% | TIM | 51 | |||||||||||||
22 | Jessica | 182 | 2914 | 44548 | 106.07% | TIM | ||||||||||||||
23 | Jessica | 178 | 3050 | 36637 | 101.67% | TIM | ||||||||||||||
24 | Cesar | 350 | 2639 | 39740 | 94.62% | TIM | ||||||||||||||
25 | Cesar | 347 | 2776 | 42258 | 100.61% | TIM | ||||||||||||||
26 | Cesar | 358 | 2604 | 42196 | 100.47% | JOHN | ||||||||||||||
27 | Cesar | 338 | 3091 | 40256 | 103.03% | JOHN | ||||||||||||||
28 | Cesar | 355 | 3019 | 37773 | 100.63% | JOHN | ||||||||||||||
29 | Cesar | 343 | 2632 | 42290 | 100.69% | JOHN | ||||||||||||||
28 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
H4 | H4 | =TODAY() |
Q4 | Q4 | =SUM(O13:O29) |
K5 | K5 | =IFERROR(AVERAGEIF($E$11:$E$120,">0.00%"),"") |
B4:B7,F4:F7,D4:D7 | B4 | =IFERROR(AVERAGEIF($A$11:$A$120,A4,$E$11:$E$120),"") |
H8 | H8 | =COUNTA($A$11:$A$120) |
N13:N29 | N13 | =IFERROR(AVERAGEIF($F$11:$F$120,M13,$E$11:$E$120),"") |
O13:O29 | O13 | =COUNTIF($F$11:$F$120,M13) |
Q13 | Q13 | =IFERROR(SUMPRODUCT(N13:N29,O13:O29)/SUM(O13:O29),"") |
Q21 | Q21 | =IF(Q13>"","",SUM($O$13:$O$29)) |
M13:M25,M27:M29 | M13 | =IFERROR(INDEX($F$11:$F$120, MATCH(0,COUNTIF($M$12:M12, $F$11:$F$120),-1)),"") |
E11:E29 | E11 | =MAX((C11/3000),(D11/42000)) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
E11:E120,K5,Q13 | Cell Value | <=0.001 | text | YES |
U13:U29,X13,K5 | Cell | contains a blank value | text | YES |
B4:B7,D4:D7,F4:F7,N13:N29,Q13 | Cell | contains a blank value | text | YES |
B4:B7,D4:D7,F4:F7 | Cell Value | >=0.97 | text | YES |
B4:B7,D4:D7,F4:F7 | Cell Value | between 0.95 and 0.969999999 | text | YES |
B4:B7,D4:D7,F4:F7 | Cell Value | <=0.95 | text | NO |
E11:E120 | Cell Value | <0.9 | text | YES |
E11:E120,K5,N13:N29,Q13 | Cell Value | >=0.97 | text | YES |
E11:E120,K5,N13:N29,Q13 | Cell Value | between 0.96999999999999 and 0.95 | text | YES |
E11:E120,K5,N13:N29,Q13 | Cell Value | <=0.94999999999 | text | NO |