Pietro Di Micio
Board Regular
- Joined
- Apr 29, 2020
- Messages
- 51
- Office Version
- 365
- Platform
- MacOS
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lrB As Long, lrE As Long
If Not Intersect(Target, Range("B7")) Is Nothing Then
Application.EnableEvents = False
Application.ScreenUpdating = False
lrB = Range("B" & Rows.Count).End(xlUp).Row
lrE = Range("E" & Rows.Count).End(xlUp).Row
Range("B7:E" & IIf(lrB > lrE, lrB, lrE)).FormatConditions.Delete
Range("E7:F" & lrE).Insert Shift:=xlToRight
With Range("E7:E" & lrE)
.Formula2 = Replace("=FILTER(G$7:H$#,G$7:G$#=B7,INDEX(FILTER(G$7:H$#,ISNA(MATCH(G$7:G$#,B$7:B$#,0))*ISNA(MATCH(G$7:G$#,E$6:E6,0)),""""),1,0))", "#", lrE)
.Resize(, 2).Value = .Resize(, 2).Value
.Offset(, 2).Resize(, 2).Delete Shift:=xlToLeft
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(E7<>"""",B7=E7)"
.FormatConditions(1).Interior.Color = 13693658
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(E7<>"""",B7<>E7)"
.FormatConditions(2).Interior.Color = 65535
End With
With Range("B7:B" & lrB)
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(B7<>"""",B7=E7)"
.FormatConditions(1).Interior.Color = 13693658
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(B7<>"""",B7<>E7)"
.FormatConditions(2).Interior.Color = 65535
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
pdm_cockpit.xlsx | ||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | |||
31 | ||||||||||||||||||||||||||||
32 | 1/31/24 | |||||||||||||||||||||||||||
33 | 2/29/24 | |||||||||||||||||||||||||||
34 | 3/15/24 | |||||||||||||||||||||||||||
35 | 3/22/24 | |||||||||||||||||||||||||||
36 | A - Purchase | B - Negotiate | C - Select | - | - | 3/29/24 | ||||||||||||||||||||||
37 | FLSM | IAE | OPP. OWNER | COMPANY | OPP. DESCRIPT. | OPP. ID | FCST CATEGORY | OPP PHASE | CLOSE DATE | NET SAP USD | LOB | PARTNER | PBM | CQ LINEARITY | OPP. ID2 | COMPANY2 | CADENCE | SQ/GAF | WHY CHANGE | WHY NOW | WHY SAP | DECISION MAKER | PROPOSAL | COLOR CODE | TABLE | |||
38 | M1 | ok | y | y | y | y | y | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||100% | 100% | |||||||||||||||||||
39 | M1 | - | - | - | - | - | - | 0% | 0% | |||||||||||||||||||
40 | M1 | - | - | - | - | - | - | 0% | 0% | |||||||||||||||||||
41 | M1 | - | - | - | - | - | - | 0% | 0% | |||||||||||||||||||
42 | M1 | - | - | - | - | - | - | 0% | 0% | |||||||||||||||||||
COCKPIT - CQ |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G36 | G36 | =SUBTOTAL(3,G38:G1055) |
K36 | K36 | =SUBTOTAL(9,K38:K1055) |
Y38:Y42 | Y38 | =REPT("|",(COUNTIF(Table1[@[WHY CHANGE]:[PROPOSAL]],"y")/5)*75)&(COUNTIF(Table1[@[WHY CHANGE]:[PROPOSAL]],"y")/5)*100&"%" |
Z38:Z42 | Z38 | =COUNTIF(Table1[@[WHY CHANGE]:[PROPOSAL]],"y")/5 |
O38:O42 | O38 | =IF(A38<=$O$32,"M1",IF(A38<=$O$33,"M2",IF(A38<=$O$34,"SC",IF(A38<=$O$35,"LW",IF(A38>=$O$36,"QE","QE"))))) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
T38:X537 | Cell Value | ="-" | text | NO |
T38:X537 | Cell Value | ="n" | text | NO |
T38:X537 | Cell Value | ="y" | text | NO |
S38:S537 | Cell Value | = "-" | text | NO |
S38:S537 | Cell Value | ="help needed" | text | NO |
S38:S537 | Cell Value | ="pending" | text | NO |
S38:S537 | Cell Value | ="ok" | text | NO |
S40:S537 | Cell | contains an error | text | NO |
P38:P537 | Expression | =IF(P38<>0;ISERROR(MATCH(P38;$G$30:$G$1000;0));0) | text | NO |
O38:O537,R38:R537 | Cell | contains an error | text | NO |
H38:H537 | Expression | =(OR(H38="Committed";H38="Probable";H38="Booked/Won")) | text | NO |
K38:K537 | Cell Value | >=$K$30 | text | NO |
K38:K537 | Cell | contains an error | text | NO |
Y38:Y537 | Expression | =$Z38=0% | text | NO |
Y38:Y537 | Expression | =AND($Z38<=49%;$Z38>=1%) | text | NO |
Y38:Y537 | Expression | =AND($Z38>=50%;$Z38<80%) | text | NO |
Y38:Y537 | Expression | =AND($Z38>=80%;$Z38<=100%) | text | NO |
G30,G38:G537 | Expression | =IF(G30<>0;ISERROR(MATCH(G30;$P$30:$P$1000;0));0) | text | NO |
L31:N35 | Cell | contains an error | text | NO |
I30,I38:I1055 | Expression | =IF(AND(L30<>0;L30<>"T&E");ISERROR(MATCH(I30;$B$36:$E$36;0));0) | text | NO |
J30,J38:J537 | Expression | =IF(AND(J30<>0;L30<>0;L30<>"T&E";H30<>0;H30<>"Booked/Won");ISERROR(MATCH(J30;$K$35:$O$35;0));0) | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
S38:S537 | List | =$S$26:$S$29 |
T38:X537 | List | =$T$27:$T$29 |
Yes, especially when the layout, data and explanation keep changing!Is it complex?