Excel Forum

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

GaryTibble

Christchurch

Member

Members

Forum Posts: 25

Member Since:
September 26, 2016

Offline

January 18, 2019 - 7:48 am

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

See Answer

Anders Sehlstedt

Eskilstuna, Sweden

VIP

Members

Trusted Members

Forum Posts: 870

Member Since:
December 7, 2016

Offline

January 18, 2019 - 4:27 pm

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))

Answers Post

GaryTibble

Christchurch

Member

Members

Forum Posts: 25

Member Since:
September 26, 2016

Offline

January 21, 2019 - 4:47 am

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

GaryTibble

Christchurch

Member

Members

Forum Posts: 25

Member Since:
September 26, 2016

Offline

January 21, 2019 - 5:59 am

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

Anders Sehlstedt

Eskilstuna, Sweden

VIP

Members

Trusted Members

Forum Posts: 870

Member Since:
December 7, 2016

Offline

January 22, 2019 - 6:23 am

Hello Gary,

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

Forum Timezone: Australia/Brisbane

Most Users Ever Online: 245

Currently Online: Alan Sidman, Ngoc Tinh, Dieneba NDIAYE, Alexandra Radu, Natasha Smith, Monique Roussouw

Guest(s) 10

Currently Browsing this Page:
1 Guest(s)