Log In
Register
Home
New Member
November 22, 2023
Offline
I have been tasked with developing an Excel register for RISKS.
The new risk format needs to be clean, clear, while still enabling users to add and edit the information.
The issue is this, the information is effectively one-to-many.
One risk can have 4x Impact Categories, Each Impact Category may have 1 to 10x Controls.
I need to be able to have users add new entries without having to be trained in the whole formatting of cell process to enable a clean view of the information.
I NEED
-to be able sort this mess of information to see any outstanding actions, what are the highest scores in specific areas, and track controls as the same ones can appear against different entries.
-Of course, I want the formulas protected for editing and reformatting.
-Also, to make it harder, I have been warned against using Macros in Excel as there is a good chance our company will outlaw their use for fear of cyber threats.
In Access I would be creating separate tables for risk, category and controls, and their linkage tables with GUI form interface.
I can visualise using a new column with unique numbering to assist sorting, Slicers to filter quickly, individual tables to make the similar linkages, and even combo boxes for selection. But I cannot seem to work out a way to display efficiently without the user having to format as they go to maintain the clean look. Maybe use of forms to update data, and combining the text with numerous carriage returns within the category cells to align with the controls.
CAN IT BE DONE!?
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Managing a risk register in Excel is not the same as using a system specifically designed for the task, it will always be limited and simpler. For projects it is usually good enough.
When designing such register I would download some templates (there are always some available, I searched for free risk register templates and found some) and see what parts I would like to have. I would also separate so that the input table is just for adding data, and without having built one myself, I think I would go with having each control on its own row, set up in a tabular format.
To present the information I would try using a Pivot Table, or perhaps using cube functions, by that we can separate the data input from the presentation, on their own files if needed.
Br,
Anders
New Member
November 22, 2023
Offline
Thanks Anders, the idea of using CUBE FUNCTION is a new one - Thanks for that. Off to research now.
And yes, the format requirement of the Risk Register is dictated unfortunately. Also underway isolating the tables for data entry as you had suggested. 🙂
Thanks again!
Mark
1 Guest(s)
