Jul 18 2017
02:05 AM
- last edited on
Jul 25 2018
09:49 AM
by
TechCommunityAP
Jul 18 2017
02:05 AM
- last edited on
Jul 25 2018
09:49 AM
by
TechCommunityAP
Apologies if this is a duplicate - my previous attempt didn't seem to post properly.
Hello!
I am trying to format a column of cells based on both the number contained within the cells to be formatted AND the value of an adjacent cell, using Office 2007.
e.g.
Males and females have different healthy ranges:
Males: between 13 and 20
Females: between 11 and 17
Column V cells say either "Male" or "Female" (from a pull-down menu).
Column W contains the numerical result entered by user, and is to be colour-formatted red if the result is outwith the appropriate gender-dependent range.
I have entered the following rule (Excel seems to have added extras quotation marks for me):
="(($W5<11 OR $W5 >17) AND ($V5 = ""Female"")) OR (($W5<13 OR $W5>20) AND ($V5 = ""MALE"")"
The above doesnt give me an error message - but it doesnt perform any formatting either!
Any help would be appreciated :)
Jul 18 2017 02:20 AM
SolutionHi Clare,
I didn't check the logic of your function, just to mention what Excel adds extra quotations since you use incorrect syntaxis for the formula, thus Excel considered it as text.
AND and OR are functions, not operators. Correct syntaxis will be =AND(a,b) not =(a AND b)
Jul 21 2017 01:05 AM
Feb 14 2019 05:16 AM
I have a similar issue. I have 2 columns K and N in my spreadsheet that I need to format. Column K has values which are conditionally formatted based on their value. However I'd like to add a further conditional format based on whether Column N has a 'y' in it and colour the cell in Column K green but cant get it to work. I have this formula in the cell ....=($N1173 ="y") as an example.
My rules are attached.
Any help appreciated.
It's a nice to have requirement rather than a must.
Thanjks
Ken
Feb 14 2019 05:41 AM
Feb 14 2019 04:02 PM
Yes, if you use the rule with the formula, you write the formula for the first cell of your range. It is applied to other cells of the range approximately the same way as if you write formula for the one cell and that drag it down. Other words you shall take care about absolute and relative references in your formula.
Jul 18 2017 02:20 AM
SolutionHi Clare,
I didn't check the logic of your function, just to mention what Excel adds extra quotations since you use incorrect syntaxis for the formula, thus Excel considered it as text.
AND and OR are functions, not operators. Correct syntaxis will be =AND(a,b) not =(a AND b)