Forum

Notifications
Clear all

Formula to handle Conditional formatting of a cell using combinations of values selected in two other columns

5 Posts
2 Users
0 Reactions
97 Views
(@garytibble)
Posts: 25
Eminent Member
Topic starter
 

Hi

Please refer to the attached spreadsheet.

Sheet "RiskData" A1:F6 is the data to be used.

Sheet "Risks": Column G uses a dropdoen list based on "RisksData"A2:A6

Sheet "Risks": Column H uses a dropdoen list based on "RisksData"B1:F1

The problem I face is:

Sheet "Risks": Column I needs to return the value from "RisksData"B2:F6 based on the combination selected in "Risks": Column G and "Risks": Column H, with "Risks": Column I conditional formatted to the colour matching the value returned from "RisksData"B2:F6

A solution and advice would be very appreciated.

Thank you, Gary

 
Posted : 18/01/2019 5:48 pm
Anders Sehlstedt
(@sehlsan)
Posts: 972
Prominent Member
 

Hello Gary,

I used an INDEX & MATCH formula to get the value for cell I2. Use conditional formatting on the cell to catch the correct colour based on the value you get.

=INDEX(RiskData!$B$2:$F$6,MATCH([@[INHERENT Likelihood]],likelihood,0),MATCH([@[INHERENT Impact]],Impact,0))

 
Posted : 19/01/2019 2:27 am
(@garytibble)
Posts: 25
Eminent Member
Topic starter
 

Hello Anders

Monday morning, just back at work.

Thanks very much for your response, and it works great - thank you.

Much easier than the way I was trying to achieve the same.

Regards, Gary

 
Posted : 21/01/2019 2:47 pm
(@garytibble)
Posts: 25
Eminent Member
Topic starter
 

Hi Anders

Further to your formula:

I added an IFERROR function around your formula to handle the #N/A in the cells to tidy up the look of the spreadsheet.

Gary

 
Posted : 21/01/2019 3:59 pm
Anders Sehlstedt
(@sehlsan)
Posts: 972
Prominent Member
 

Hello Gary,

Yes, it is always good to include some error handling functions, just in case. Great that the formula worked fine for you.

 
Posted : 22/01/2019 4:23 pm
Share: