hugoexcel22
New Member
- Joined
- May 31, 2022
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
Good morning,
I'm making a report with a summary sheet where the data must be displayed, I already made 1 column that works perfectly fine and other one that is not working properly.
I have this formula:
=IF(ISNUMBER(MATCH(SHEET2!$A:$A;A1;0))*ISNUMBER(MATCH(SHEET2!$B:$B;B2;0))*ISNUMBER(MATCH(PENDING!$L:$L;{"CRITICAL"};0));{"CRITICAL"};"NOT FOUND")
For other cells I would have to add this also:
=IF(ISNUMBER(MATCH(SHEET2!$A:$A;A1;0))*ISNUMBER(MATCH(SHEET2!$C:$C;C2;0))*ISNUMBER(MATCH(SHEET2!$B:$B;B2;0))*ISNUMBER(MATCH(PENDING!$L:$L;{"CRITICAL"};0));{"CRITICAL"};"NOT FOUND")
I also would need it to read not only the "CRITICAL" but also if it contains MEDIUM-HIGH, MEDIUM, LOW and IG, but I don't know how to add that (is not even working now so...)
That returns a "SPILL", what I need is the following:
This is sheet 1 where I have to put the formula because it is the summary, and this is sheet 2 where the data is taken:
Basically I need to show on Sheet1 the summary of the data from Sheet2, I have 2 main values: Thing A and Thing B, but Thing A can be linked to Thing B or C, the status of the Thing can be 4: Unresolved, Resolved, PPVV and Pending, which also can have 5 possible severity results CRITICAL, MEDIUM-HIGH, MEDIUM, LOW, IG. What I need to show on Sheet1 is the most serious, I have used the following formulas for the other column that does not appear and it works perfectly:
=SUMPRODUCT(ISNUMBER(MATCH(SHEET2!A:A;A1;0))*ISNUMBER(MATCH(SHEET2!B:B;B2;0))*ISNUMBER(MATCH(SHEET2!M:M;{"UNRESOLVED";"PPVV";"PENDING"};0)))
=SUMPRODUCT(ISNUMBER(MATCH(SHEET2!A:A;A1;0))*ISNUMBER(MATCH(SHEET2!C:C;C3;0))*ISNUMBER(MATCH(SHEET2!B:B;B2;0))*ISNUMBER(MATCH(SHEET2!M:M;{"UNRESOLVED";"PPVV";"PENDING"};0)))
What I need is something similar but output the specified text based on existing unresolved rows.
If I'm not being clear please tell me.
Regards.
I'm making a report with a summary sheet where the data must be displayed, I already made 1 column that works perfectly fine and other one that is not working properly.
I have this formula:
=IF(ISNUMBER(MATCH(SHEET2!$A:$A;A1;0))*ISNUMBER(MATCH(SHEET2!$B:$B;B2;0))*ISNUMBER(MATCH(PENDING!$L:$L;{"CRITICAL"};0));{"CRITICAL"};"NOT FOUND")
For other cells I would have to add this also:
=IF(ISNUMBER(MATCH(SHEET2!$A:$A;A1;0))*ISNUMBER(MATCH(SHEET2!$C:$C;C2;0))*ISNUMBER(MATCH(SHEET2!$B:$B;B2;0))*ISNUMBER(MATCH(PENDING!$L:$L;{"CRITICAL"};0));{"CRITICAL"};"NOT FOUND")
I also would need it to read not only the "CRITICAL" but also if it contains MEDIUM-HIGH, MEDIUM, LOW and IG, but I don't know how to add that (is not even working now so...)
That returns a "SPILL", what I need is the following:
PCI PENDING GAPS.xlsx | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | THING 1 | THING 2 | ASSET | SERVICE | CURRENT STATUS | ||
2 | THING A | THING A2 | - | - | #SPILL! | ||
3 | THING B | THING B2 | ASSET1 | SERVICE1 | #SPILL! | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2 | E2 | =IF(ISNUMBER(MATCH(VULNERABILITIES!$A:$A,STATUS!A2,0))*ISNUMBER(MATCH(VULNERABILITIES!$B:$B,STATUS!B2,0))*ISNUMBER(MATCH(VULNERABILITIES!L:L,{"CRITICAL"},0)),{"CRITICAL"},"NOT FOUND") |
E3 | E3 | =IF(ISNUMBER(MATCH(VULNERABILITIES!$A:$A,A3,0))*ISNUMBER(MATCH(VULNERABILITIES!$B:$B,B3,0))*ISNUMBER(MATCH(VULNERABILITIES!L:L,{"CRITICAL"},0)),{"CRITICAL"},"NOT FOUND") |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
VULNERABILITIES!_FilterDatabase | =VULNERABILITIES!$A$1:$N$190 | E2:E3 |
This is sheet 1 where I have to put the formula because it is the summary, and this is sheet 2 where the data is taken:
PCI PENDING GAPS.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | THING 1 | THING 2 | ASSET | SERVICE | SEVERITY | STATUS | ||
2 | THING A | THING A2 | - | - | CRITICAL | UNRESOLVED | ||
3 | THING B | THING B2 | ASSET1 | SERVICE1 | MEDIUM-HIGH | UNRESOLVED | ||
Sheet2 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
F1:F3 | Cell Value | contains "UNRESOLVED" | text | NO |
F1:F3 | Cell Value | contains "PENDING" | text | NO |
F1:F3 | Cell Value | contains "PPVV" | text | NO |
F1:F3 | Cell Value | contains "SOLVED" | text | NO |
E1:E3 | Cell Value | contains "CRITICAL" | text | NO |
E1:E3 | Cell Value | contains "MEDIUM-HIGH" | text | NO |
E1:E3 | Cell Value | contains "MEDIUM" | text | NO |
E1:E3 | Cell Value | contains "LOW" | text | NO |
E1:E3 | Cell Value | contains "IG" | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
E2:E3 | List | CRITICAL;MEDIUM-HIGH;MEDIUM;LOW;IG |
F2:F3 | List | UNRESOLVED;SOLVED;PPVV;PENDING |
Basically I need to show on Sheet1 the summary of the data from Sheet2, I have 2 main values: Thing A and Thing B, but Thing A can be linked to Thing B or C, the status of the Thing can be 4: Unresolved, Resolved, PPVV and Pending, which also can have 5 possible severity results CRITICAL, MEDIUM-HIGH, MEDIUM, LOW, IG. What I need to show on Sheet1 is the most serious, I have used the following formulas for the other column that does not appear and it works perfectly:
=SUMPRODUCT(ISNUMBER(MATCH(SHEET2!A:A;A1;0))*ISNUMBER(MATCH(SHEET2!B:B;B2;0))*ISNUMBER(MATCH(SHEET2!M:M;{"UNRESOLVED";"PPVV";"PENDING"};0)))
=SUMPRODUCT(ISNUMBER(MATCH(SHEET2!A:A;A1;0))*ISNUMBER(MATCH(SHEET2!C:C;C3;0))*ISNUMBER(MATCH(SHEET2!B:B;B2;0))*ISNUMBER(MATCH(SHEET2!M:M;{"UNRESOLVED";"PPVV";"PENDING"};0)))
What I need is something similar but output the specified text based on existing unresolved rows.
If I'm not being clear please tell me.
Regards.