Forum Discussion
Applying a set of rules if a condition based on a different column, same row, is satisfied
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...)
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!
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
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!
RICARDO JULIO RODRIGUEZ FERNANDEZ
In attached Wbook see sheet 'New' where I had to format your data as Table (https://answers.microsoft.com/en-us/msoffice/forum/all/use-tables-not-ranges/992a6e69-9c06-4b01-9230-1e12a87b81b2) + 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
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!