Require Formula to Fill Based on Other Cells Variable Data

[Canadian911Guy]

Good Morning!

I'm having trouble building a formula to make the following occur automatically:

I have uploaded a workbook...

On sheet HRVA, when the user selects any of the combinations noted in Columns E&F and/or Columns J&K it should automatically fill and format the correct number and colour into Columns G&L (respectively) with information from the RISK RANKING sheet, but for the life of me I can't get any iterations of my forumlai to make that happen.

Any help with this will be met with loads of GOOD KARMA!

Thanks!

[63falcondude]

This is a two part question. How to get the number and how to get the color.

How to get the number:
Replace the "Alt-enter" in C7 of the 'Risk Ranking' sheet with a space.
G3 =IFERROR(INDEX('Risk Ranking'!$D$7:$H$11,MATCH(E3,'Risk Ranking'!$C$7:$C$11,0),MATCH(F3,'Risk Ranking'!$D$12:$H$12,0)),"")
You can copy/paste G3:G27 to L3:L27.

The color will involve 4 or (5 if you want to bring over the borders) Conditional Formatting rules.

Working on those now.

[63falcondude]

How to get the color:

Green
Highlight G3:G27 > Conditional Formatting > New Rule
=OR(E3&G3="Rare1",E3&G3="Rare2",E3&G3="Unlikely2",E3&G3="Unlikely4",E3&G3="Moderate3")
Format: Green > OK > OK

Yellow
Highlight G3:G27 > Conditional Formatting > New Rule
=OR(E3&G3="Rare3",E3&G3="Unlikely6",E3&G3="Moderate6",E3&G3="Moderate9",E3&G3="Likely4",E3&G3="Likely8",E3&G3="Almost Certain5")
Format: Fill Yellow > OK > OK

Orange
Highlight G3:G27 > Conditional Formatting > New Rule
=OR(E3&G3="Rare4",E3&G3="Rare5",E3&G3="Unlikely8",E3&G3="Unlikely10",E3&G3="Moderate12",E3&G3="Likely12",E3&G3="Almost Certain10",E3&G3="Almost Certain15")
Format: Fill Orange > OK > OK

Red
Highlight G3:G27 > Conditional Formatting > New Rule
=OR(E3&G3="Moderate15",E3&G3="Likely16",E3&G3="Likely20",E3&G3="Almost Certain20",E3&G3="Almost Certain25")
Format: Fill Red > OK > OK

If you want to include the borders, you will have to include them in the formatting and split orange up into 2 rules (one for each border color).

Then repeat for column L.

[63falcondude]

Attached, is the result from posts #2 and 3 (for column G only).

[Canadian911Guy]

You are getting some really good karma!!
Thank you!!

[Canadian911Guy]

Good Day!

I'm reopening this because it's for the same workbook with a similar problem. I've filled in the results as they should be.
In addition to the awesome help that @63falcondude gave me, I also now require the H & M columns to fill with either YES or NO based on the COLOUR & NUMBER of the G & L columns. So if the G&L columns are ORANGE with either 4, 5, 8, 10, 12, or 15; then I need a formula that will fill in either YES or NO in the H&L columns.

Once again, I have tried various iterations of what formulas @63falcondude provided but I can't get anything I build to work.

Thank you in advance for all your help and advice!

[jeffreybrown]

Hi Canadian911Guy,

Could you please take a few minutes and follow the forum rules to craft an appropriated thread title!!

[63falcondude]

Try this in H5:

=IF(OR(F5&G5={"Moderate10","Significant12","Significant15"},F5={"Major","Catastrophic"}),"YES","NO")

or better yet:

=IF(OR(G5>9,F5={"Major","Catastrophic"}),"YES","NO")

[Canadian911Guy]

Once again you get the GOOD KARMA my friend!

P.S.: I wasn't using {} and I had F5,G5 instead of F5&G5...

Thanks for the help!

[63falcondude]

Happy to help!

[jeffreybrown]

Moderator Didn't Like Previous Title

No need to get snippy. Just pointing out the forum rules according to thread titles.

[Canadian911Guy]

Deleted as no longer relevent