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.
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?
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.
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...