Jul 16 2021 12:59 PM
I have copied this formula - =@IF(OR($M10="",$L10=""),"",INDEX(Matrix_Rating,MATCH($M10,Matrix_Likelihood,0),VALUE(LEFT($L10))))
from one Excel spreadsheet to another to perform the same Risk Rating task, however it does not work on the second Excel spreadsheet. I have checked to see if the original sheet has a reference page but it doesn't. So I am confused as to why this formula works on one Excel spreadsheet and not the other. Can you help me please.
Jul 16 2021 03:36 PM
Jul 17 2021 12:21 PM
Yes your right. It must refer to that "Risk_Matrix" in another document.
So I guess I'll have to do it using an inbuilt table in this document.
Thanks for your help.
Jul 17 2021 12:25 PM
You can define that range in the Risk_Matrix Sheet as a named range "Matrix_Rating and your formula should work as is.
Jul 17 2021 02:30 PM
Jul 17 2021 02:31 PM
Jul 17 2021 02:41 PM
Jul 17 2021 03:06 PM
I downloaded the file you shared, I will study it and I will ask you questions to verify if my comprehension to the item is correct before applying the appropriate formula
Jul 17 2021 03:19 PM
Jul 17 2021 03:56 PM
Here you go. The formula is:
=INDEX(Consequence,MATCH(LEFT('Electrical-Commission '!W15,1),LEFT('Risk Matrix'!$A$5:$A$10,1),0),MATCH(LEFT(X15,1),LEFT('Risk Matrix'!$A$5:$F$5,1),0))
and it seems to bring the color along with the color in the matrix as well.
Jul 17 2021 08:19 PM
Hello Yea,
Thanks for that doing this for me.
When I put the formula into the first Risk rating cell "Y15" and press enter the following error message comes up. Can you send back the document that you have corrected please.
Jul 17 2021 08:40 PM
I did it was attached to my last response, look at the bottom of my previous response.
Jul 17 2021 08:55 PM
SolutionJul 17 2021 11:02 PM
Jul 17 2021 11:05 PM
Jul 17 2021 11:28 PM
Jul 17 2021 11:30 PM
Jul 17 2021 08:55 PM
SolutionThis is the workbook that i sent on my response, and it works. Isn't it the right value?