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?

 

[Gerald Higgins]

Hi, there seem to be at least two challenges here -
1) Identifying that a change in the value of F8 has actually occurred
2) Making the right colour and direction of arrow appear.

For (1) . . .
How are you doing this ?
I can think of maybe 3 ways
(1) Purely manual - someone looks at the value and manually compares it with the previous version
(2) Semi-automatic - the previous value is stored somewhere, then compared via a formula with the latest value. From time to time, old values are over-written with new values. You can probably write code to do this.
(3) Fully automatic - some kind of code identifies whenever a change in the value occurs. Personally I don't know how to write code for that, but others on this board probably can.

(2) How are you drawing the arrows ?
Using shapes ?
Or using special characters within a symbols font ?

 

[halesowenmum]

Hi there. Yes, this dual aspect is what stumped me from figuring out how to proceed!

I guess based on how the sheet is used, the overall calculated score only changes if someone comes in and manually changes one of the values in the columns that contribute to the calculated score. Ideally, as soon as they change one of them up or down, ideally I'd like the arrow to change as necessary - if we leave it to people and give it to them as another field to fill in, they're less likely to do than if it just happens.

Re the drawing of the arrows, I really don't mind so long as they're clear and easy to see and people don't have to follow any steps to select the one they want - fairly automated selection of an arrow by any means once the values are changed.

 

[Gerald Higgins]

OK, let's deal with drawing the arrows first.

Personally I would use a character from a symbol font.
Lots of fonts have arrow characters, including SAPDINGS, SYMBOL, WEBDINGS, WINGDINGS, and WINGDINGS3.
Pick a font that you like, and then you can paste the characters into your worksheet.
You can do this from your PC's Character Map tool.

You can either encode the symbol itself, or you can use the code for the symbol.

For example, the SYMBOL font has an up arrow with code 0xDD.
If you paste this character into Excel, and format as a text code, it appears like this

Ý

and you can use that in a formula, like this

=if(A1< A2,"Ý","")

Format the cell using the SYMBOL font, and it should show an up arrow if A1 is less than A2.

OR, you can use the character code in your formula, like this.
You'll need to work out the Excel code for this character, you can do this by pasting the character into a cell, let's say B1.
Then use this formula

=code(B1)

to return the numeric code for this character, which is 221

=if(A1< A2,char(221),"")

If you can get this bit working, to show the three types of arrows that you want, then we can move on to identify if a change in the value has occurred.

 

[halesowenmum]

Ok I think i've cracked that although I'm not sure of the cell references as I'm not comparing two figures against each other, just whether the calculated total score went up because the user edited the values in likelihood, impact or proximity - so the formula looks healthy but currently I've got it comparing col F and col G and it doesn't return an arrow).

 

[Gerald Higgins]

Yeah don't worry about the calculation yet, the first part was just about selecting the arrow symbols you wanted to use.
If you're happy that you've done that, we can move on.
Are you OK with the arrow symbols ?

 

[halesowenmum]

Definitely. Let's go for it.

 

[Gerald Higgins]

So what I would do is something like this.
(By the way, there are probably better ways of doing this).

I would set up an area somewhere on the worksheet (which could be hidden), to store the PREVIOUS version of the relevant values.
Then use a formula to work out whether the CURRENT value is the same, or higher, or lower, than the previous version.
Make the appearance of the arrows depend on that formula.
You would need some way of updating the previous version of the values.

For example, let's say the current values are in cells A1:A10.
Let's say you decide to store the previous values in cell Z1:Z10 (and you can then hide column Z if you like).
You can use a simple formula to work out whether the value in cell A1 is higher, lower, or the same as Z1.
And you can then use a macro to copy the values from column A to col Z from time to time, let's say every day, or every week, or however often you update this thing.

Do you think this general approach could work for you ?

If yes, we can move on to the details.

 

[halesowenmum]

Hi.

That's kinda what I'm trying to avoid because let's say a user comes into this document and changes a value in one of the contributing columns, the calculated total score therefore changes too - but there'd have to be some automated way of Excel saying, "Ah, that score's changed, let me go back and get the score as it was before, move to to the adjacent column and reinput the value the user just entered". I can't see how that would work to be honest.

It's got to be much more 'automated' than I think this suggestion is - sorry.

I think it may need to be VBA that will 'know' when a value has changed apply whatever it/the formula/CF can do to then change the arrow.

And that I have NO idea how to do!!

 

[halesowenmum]

Is there any kindly soul out there who can think of a way of achieving this?