Risk Register Heat Map

Hi,
I saw this early today, and loved it, but I can't for the life of me get it to work. Essentially it uses VBA to create a function (all very understandable), and then it responds to changes in the data. It is awesome. I saw someone had a problem with it and they posted a question 6 months ago, but there hasn't been any response.

Can someone please help? I would be incredibly grateful! (File attached, and link attached)

(Also please look at VBA function code)...

You will see all of my #Value errors in the worksheet.

If I need to further clarify my query - please let me know - just trying to be short and sharp!

Many Thanks!


https://www.youtube.com/watch?v=_XqCX4pKFPM

try changing the function code to:

Please Login or Register  to view this content.

Happy to help

Originally Posted by tasboxster

I am not sure how to express my gratitude!

You could click "Add Reputation"

Originally Posted by nickmax1

Correct me if I am wrong, but the heatmap is not accurate with that code.
It puts the numbers in the right Consequence rows but not the right Likelihood row.
for example Nr 18 is placed in cell G3. It should feature in H3 as it is Severe and Likely, but has been placed in the Severe/Possible box.

The code places based on the number values, not the labels.

There is a mismatch in the OPs workbook: Likelihood labels / values used for the data don't match those used in the grid.

So, taking #18 as an example:
- Consequence: Severe 4
- Likelihood: Likely 3

This gets placed correctly based on the numbers (4,3) but the labels don't match.

Tkansk for advice the rules.Excuseme.

Hi. You joind the Forum a few minutes ago and agreed to adhere to the rules. already you've broken one of them.

It's a forum rule that you start your own thread, rather than try to "hijack" someone else's thread. This helps prevent massive confusion arising -which will happen if it is not clear which question is being answered. So please start your own thread and explain your own problem (make sure that, right from the start, you use a meaningful title (NOT things like "help needed" or "urgent problem"... think of the Google search terms that you would use to find the solution). Attach an Excel sheet. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!


Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.