Excel 2016
We're testing parts for different client and the data is in one spreadsheet for this test.
ColumnA has part numbers that all start with client identifier, ColumnB is where results are input as a number with 3 decimal points. Each client has a different acceptance range for test results. I want to have ColumnB highlight red if it's out of scope for that client.
To test my formula, I use ColumnC because I tend to use a period instead of a comma. In C2, is have this formula that works fine for what I'm trying to do. If the part number starts with that client identifier and is under a threshold, it says Ford. I can change the number and the client ID and get the proper reaction. I can string together the multiple AND statements with some OR statements with no issue
=IF(AND(LEFT(A2,4)="Ford",B2<0.8),"Ford","notford")
If I use the following for conditional formatting, it does nothing
AND(LEFT(A2,4)="Ford",B2<0.8)
Is there an issue with conditional formatting referencing it's own cell or something obvious I am missing?
TIA
We're testing parts for different client and the data is in one spreadsheet for this test.
ColumnA has part numbers that all start with client identifier, ColumnB is where results are input as a number with 3 decimal points. Each client has a different acceptance range for test results. I want to have ColumnB highlight red if it's out of scope for that client.
To test my formula, I use ColumnC because I tend to use a period instead of a comma. In C2, is have this formula that works fine for what I'm trying to do. If the part number starts with that client identifier and is under a threshold, it says Ford. I can change the number and the client ID and get the proper reaction. I can string together the multiple AND statements with some OR statements with no issue
=IF(AND(LEFT(A2,4)="Ford",B2<0.8),"Ford","notford")
If I use the following for conditional formatting, it does nothing
AND(LEFT(A2,4)="Ford",B2<0.8)
Is there an issue with conditional formatting referencing it's own cell or something obvious I am missing?
TIA
_local archive Carburizing Metallurgical Record.xlsx | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | part | %c | |||
2 | Ford13 | 0.500 | Ford | ||
3 | chevy14 | 0.750 | |||
4 | dodge15 | 0.825 | |||
Sheet4 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C2 | C2 | =IF(AND(LEFT(A2,4)="Ford",B2<0.8),"Ford","notford") |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
B2 | Expression | ="AND(LEFT(A2,4)=""Ford"",B2<0.8)" | text | NO |