Moving Text Based on Cell Sum

Copper Contributor



I'm trying to figure out how I can move text based off a number.



As you can see there are 5 points on the left hand side, next to each is a column for S (Severity) and L (Likelihood), the final column = R(Risk)  SXL = R.

This gives me a number in the final column, if the number is between 1-3 then its Low, 4-6 is medium and 7-9 is high. From this point I want it to highlight the relevant cell underneath to show on average what the figures are, for example, if 3 of the numbers are in the high bracket then highlight the "High" box below red etc.


From here, there is "Additional Control Measures" underneath, for any of the points above that are in the high category I want this to copy the text form here and copy into the first cell in additional control measures. No idea how to do this.


Any help would be greatly appreciated. Below is an example.




3 Replies

Hi @DylanBrown 


This might not be exactly what you want (yet) as I'm not sure I understood ' show on average what the figures are'



You'll see in attached file (in Name Manager) a formula named AvgInitialAssessment that does the AVERAGE of all numbers in [R]. If this isn't what you meant please explain how you want to calc. this average


Also, in table 'Additional Control Measures' I used old style formulas as you did not mention the version of Excel you run. If you run 2021 or 365, instead of a table this can be reported as a dynamic array


Thanks, this is what I needed and it works as I need it too.

Hi @DylanBrown 


There was a glitch in the initial version I posted. I uploaded a revised version later. Just in case re-download and if this does what you want there's a link at the bottom of the post to Mark as solution (helps people who Search) Thanks