Increase / decrease arrows based on what user types in

[halesowenmum]

Hi.

I have a RAID log and we wish to allow risk owners to indicate when a risk has increased or decreased in severity (in their opinion). So they would review each risk and for any that they felt had got better or worse, they'd be able to say "this one's getting worse", "this one's gotten better" and when they did that, the appropriate arrow would appear in a different column.

They'd be editing the Impact (C8) and/or Likelihood (D8) / and/or Proximity (E8) scores already there, updating them to what they thought the severity was now, today. That would give a new score that was either higher or lower than what was there previously.

What I'd like is for whenever they update one of these columns described above, such that the total score changes (F8), it automatically changes the arrow accordingly to show if it has gone up or down, or, stayed the same. This could happen within the existing column F, or it can be an additional column to the immediate right, column G.

If it was a score of 25 but it's gone up to 30 it would show a red up arrow

If it was a score of 30 but it's gone down to 25 it would show a green down arrow

Two possible conditions would determine that a black horizontal double arrow-headed arrow was in F8 or G8:
1. The score for an existing risk does not change
2. A new risk is added whereby this no change arrow would be the default.

The score resides in F8. The arrows would need to appear with the calculated total score in F8, or separately in G8.

I have seen an example using just the number format, and the Custom number option but it only accommodates higher and lower, not the 'the same' arrow that I also need. Could this (shown below) be adapted or is there a better way?:
​

​

[red]*0.00% [green]*0.00%
(where I've put the great big red asterisks is where the copy and pasted up or down arrow symbol would go). My data is basic numerical values though btw, not percentages.

Anyone got any fab ideas?

 

[halesowenmum]

Hi everyone - here we are, another Monday!

I wondered if anyone had any thoughts on my question of how it could be done?

 

[Swayzy]

I would use the worksheet change event to Always copy the final output to a range were the last two updates are stored then run the arrow comparison off those two ranges.

So user makes a change in A1, the code copies that value to Y1 and at the same time moves the old value (from the previous user change) to X1.

Then you would have the last to updates. If you would like you could use a hidden sheet to store all updates and analyze too then just use a unique count to find the most recent valuea to compare against.


So yes the problem is quite solveable!

 

[halesowenmum]

Thank you! Yes, this is I think where my mind was going.

So we're talking VBA here.... what would that look like and how would a novice user of VBA go about this?

 

[RoryA]

I think your biggest problem will be determining when to update. If you have three components that make up the score, presumably they could update any combination of those, and in any order, and you only want the change of the final calculation to be relevant?

 

[Swayzy]

I agree that is a consideration. To handle it I would suggest OP to use a input form to force the user to update values in what way is needed to not get wrong results. Or use the same change event to track changes and only update the shown result after the set criteria is fulfilled.

 

[halesowenmum]

Hi. I think when to update the arrows is actually easy to determine (or at least I think it is) - if any of the scores are changed, the overall calculated score will also change, and we would be working off that column - so that's good from what I can see.

What I was thinking though is, the guidance I have written around updating scoring and how to manage risk items; I've advised people that if the Proximity date of the risk is coming due, they need to re-evaluate all the scoring and change the 'Date Raised' to today's date (manually) so that when they refresh with the new proximity score, the event will push out correctly to in 3 months time or in 6 months time, whatever they have chosen. Without the adjustment to the date raised I don't think that would be easy to do/would work?. Is there a way that a message could pop up if they change the PROXIMITY score (and this is single score-specific in this query) telling them that "You must change the 'Date Raised' first, then select the new proximity score"??

If this is a VBA solution which it's looking like it is, I have absolutely NO idea how to do that or what the code would look like...

 

[halesowenmum]

Anyone got any thoughts that could assist?