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. Th...
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 🙂
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 🙂