Increase / decrease arrows based on what user types in

halesowenmum

Active Member
Joined
Oct 20, 2010
Messages
380
Office Version
365
Platform
Windows
Hi everyone - here we are, another Monday!

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

Swayzy

Board Regular
Joined
Mar 30, 2018
Messages
77
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!
 
Last edited:

halesowenmum

Active Member
Joined
Oct 20, 2010
Messages
380
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,532
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

Board Regular
Joined
Mar 30, 2018
Messages
77
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

Active Member
Joined
Oct 20, 2010
Messages
380
Office Version
365
Platform
Windows
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...
 

Forum statistics

Threads
1,085,429
Messages
5,383,628
Members
401,843
Latest member
stevensmith1

Some videos you may like

This Week's Hot Topics

Top