Forum Discussion
3D references
Hi all, I have a task where I have to pull information from a risk assessment report in excel to a summary page. The risk assessment itself is conditionally formatted so that concerns show in red. This automatically fills a column of the sheet with mitigations, it is the mitigations I need to pull through to a different tab. Is there any way this can be done?
- Thanks to LeonPavesic; PeterBartholomew1 and mtarler I now have lots to try when I go into the office tomorrow
- Hi Liz, you're talking my language, lol. (I'm quality and regulatory). That said, you mentioned 3D references so I'm assuming you have multiple tables across multiple tabs. Maybe broken out based on general risk catagories? I would actually recommend you move away from that format and merge them all into a single table and use a column to identify the category. Then it is easy to 'filter' by that category or create a report based on that category but it will make your risk analysis and calculations much easier if it is a single table.
I also not following the comment 'automatically fills a column of the sheet with mitigations'. I have never been able to autofill mitigations but maybe you have a lookup table based on the type of risk. As for the red conditional I'm assuming that is based on a risk value (P x S or maybe P1 x P2 x S or do you use a lookup table or other calculation?) In any case, I'm not sure that matters as if you just want to pull a 'mitigation report' you can just check if that value is <>"". If it was a single table that would be particularly easy. On multiple tabs, Peter gives you the trick of using VSTACK to pull data from 3D reference into something other excel formulas can handle.
While I'm at it, I recommend if you are using P x S or another simple math calculation to find risk value to do a check. SUM( risk values) vs SUMPRODUCT( Probabilities, Severities ) to catch some simple mistakes like someone overwriting a formula with a fixed value. it also looks good to the auditor(s) to show that extra check 🙂 If your tables are aligned you could define them as a 3D range 'riskTables' (say) and append them using VSTACK. That would provide a basis for filtering the combined table
= LET( actions, TAKE(VSTACK(riskTables),,-1), required, FILTER(actions, actions<>""), required )That is combine the tables and choose the column you are interested in. Then filter out all blank fields to provide a summary across projects.
Hi Liz_Park,
To extract mitigations from a risk assessment report in Excel, especially when the report uses conditional formatting to highlight concerns in red, you can use the following steps:
- On the summary page where you want the mitigations to be displayed, in the cell where you want the first mitigation to show up, enter the following formula:
=IF(Sheet1!B1=TRUE, Sheet1!A1, "")​This formula checks if the cell in column B of "Sheet1" is in red (indicating a concern), and if so, it displays the corresponding cell from column A, which should contain the mitigations. If it doesn't meet the condition, it shows an empty string.
Hit Enter to apply the formula.
Use the fill handle (it's a small square at the bottom-right corner of the cell) to drag the formula down to fill all the cells in your summary sheet where you want the mitigations.
This process will populate your summary sheet with the mitigations from "Sheet1," precisely where the red conditional formatting indicates concerns.
Please note that you will need to customize the sheet names, column references, and ranges as per your specific Excel workbook setup.
Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.If the post was useful in other ways, please consider giving it Like.
Kindest regards,
Leon Pavesic
(LinkedIn)
