Applying a set of rules if a condition based on a different column, same row, is satisfied

[RICARDO JULIO RODRIGUEZ FERNANDEZ]

Hi all! I've been struggling for hours to find an answer but have found nothing. I've found how to apply a rule based on a cell in a different column, but I'm not able to see how to apply a set of rules to the cells in a column that satisfy a condition in a different one. See below:

How could I apply a set of rules to each cell on column Expediente only when stem = 1?

Thanks in advance!

[#2]

Hi @RICARDO JULIO RODRIGUEZ FERNANDEZ 

From your picture it seems your data (excluding headers) start in row #2, otherwise adjust 2 below:

• Select your [Expediente] column range
• Home > Cond. Format. > New Rule... > Use a formula to determine which cells to format

Formula:

=$J2 = 1

don't forget the $ before J 

[#3]

@LorenzoThanks! Yes, I can understand how I can apply a format rule to a column based on the values of a different one. But how can I apply formatting rules only when satisfying a condition? See below.

I want the formatting rules applied to column L [average] to affect only the rows with [stem] = 1. Is that possible? Thanks!

[#4]

@RICARDO JULIO RODRIGUEZ FERNANDEZ 

I want the formatting rules applied to column L [average] to affect only the rows with [stem] = 1. Is that possible?

What I provided should do it but your picture seems to indicate you already have other Cond. Format. rules applied on column [average]. Suggestion: delete any existing Cond. Format. rules that currently applies on the [average] column then apply what I suggested

If still not good please attach a sample workbook + a picture showing the expected result. Thanks

[#5]

@Lorenzo Thanks! I think the way you suggest will be more straightforward: I'm attaching a dummy spreadsheet with a single conditional format applied to column F, [ects_pass_p]. Based on the value of the cells A7 and A21, [stem] = 0, I want the conditional formatting set to column F not to affect the cells F7 and F21.

Is that possible? How? Thanks!

[#6]

@RICARDO JULIO RODRIGUEZ FERNANDEZ 

In attached Wbook see sheet 'New' where I had to format your data as Table (Benefits of using Tables) + Defined a couple of things in the Name Manager to ensure this will work when new rows are added...

This looks good to me But there's an uncertainty regarding whether (or not) figures in [ects_pass_p] should be considered to calc. the TopN 8 values when [stem] = 0. Currently whatever is in [stem] is taken in account to calc the TopN 8

[#7]

Thanks, @Lorenzo! It is not easy for me to understand how things work! After studying the new sheet for a while, I found the Name Manager, and things are a bit clearer now.

Concerning what values must be considered, figures in [ects_pass_p] must be taken into account only when [stem] = 1, and the format must be applied to the top 8. I realize now that the objective is no longer to apply the format conditionally, but to correctly select the values and apply the format to all of them. How must I modify the rule to get the 8 top values formatted?

Although I see you are able to get what I'm trying, the interface and the concepts are very confusing to me. For instance, I'm attaching a new release of the workbook. I added a new rule to [ects_pass_p]. I'm not able to understand why it doesn't accept the appearance of that column, whatever order is set.

Thanks for your time!

@Lorenzo 

[#8 · Accepted solution]

@RICARDO JULIO RODRIGUEZ FERNANDEZ 

Updated the Cond. Format. rule I earlier shared according to what I understood - Hope this is OK this time

Updated your 2nd rule according to what I suspect you want to get (not sure at all...)

[#9]

Although I marked as the best response, I want to thank you for your help! Your solution introduced to me several very useful concepts I was not using yet. A lot to learn about Microsoft tool scape! Thanks!