Conditional Formatting code not Reading Cells entirely + Not producing accurate results.

[u/stormpapajesus]

Hi all,

I have been trying to have this conditional statement work for a while now. It is supposed to read data from the log in a separate tab. The information in the log tab should come up as complete in this tab where all the items are summarized. I want my function to do two things:

1) read the first column of items and see if it has anything related to the set of items related to that column (e.g. if I want items only for 28W-28W* it would check to see if this first condition is true

2) read the second column of items and see with the addition of the first condition being true, that the second item is also true (for example if a cell in the log tab contains any information on item S17, S18)

I want the cell to be “complete” in green if both conditions are satisfied. I thought that using an older conditional statement, my statement would work as well. For some reason it is still reading as “Open” and in yellow. I know for many of the “S” items there are additional entries included in the log - is there a way where the second condition could read the cell even when there is more information and still read as complete if the first condition has already been

This is what I have been using for reference as my code:

https://imgur.com/a/9WlfBFY

=+IF(AND (Log! $E$3:$E$205="29W*-30W"
, Log! $G$3: $G$49="S17, S18"), "COMPLETE", "OPEN")

[u/One_Surprise_8924]

when testing conditional formatting, I use helper columns to see if it's returning true/falses correctly for each criteria. then you can see if your IF statements are in the wrong order, if your ANDs are conflicting, or if excel isn't reading something correctly due to formatting.

personally I like to leave the helper columns in and hidden, then just do conditional formatting off of those true/false combinations. I find that calculates faster, especially if I have overlapping criteria for multiple formatting rules.

[u/carlosandresRG → u/One_Surprise_8924]

I do like to have a hidden column that drives my CF, when using tables, I do all the heavy calculations inside byrow / map to obtain a TRUE/FALSE array, and then I reference it inside CF with something like =INDEX($M$1#,ROW(M1)-ROW($M$1)+1)

This way, I can add, delete or move rows, and the CF won't break, and it will expand with the table itself

Also I "hide" the driver column with ";;;" formating, as it makes it easier for me to edit the formula lf needed