Simplifying conditional formatting based on multiple possible text entries.

[sc204]

This is a follow up question on a solution I obtained here many years ago (One of many, thank you)

I have a very long conditional formatting equation for a group of cells that format a cell with a text entry based on the text entry of the cell to its left. The text are simple letters.
For example if the cell in question is D and the cell to the left is any of the letters A, T, P, U, H, E, or N the cell with D in it will format to Yellow. The problem is that I have 6 different letters that can be in place of D and still need to highlight yellow so I have an OR(AND statement in the formula bar.
A section of it would look like this

=OR(AND(C5="D",B5="A"),AND(C5="Z",B5="A"),AND(C5="F",B5="A"),AND(C5="Q",B5="A"),AND(C5="B",B5="A"),AND(C5="G",B5="A"),AND(C5="D",B5="T"),AND(C5="Z",B5="T"),AND(C5="F",B5="T"),AND(C5="Q",B5="T"),AND(C5="B",B5="T"),AND(C5="G",B5="T"))

I am using a statement for every possibility

Is there a proper way of simplifying the formula. I want to be able to say if C5 is "Z", or "D" or " F" etc and B5 is "A", or " T", or "U" etc to format C5 to yellow.

Thank you,
Stuart

[6StringJazzer]

You did not give a complete list of the valid letters (you can't write a formula with "etc") so I can't give you a complete answer but here is the idea.

Please Login or Register  to view this content.

[sc204]

Appreciate the quick reply and I think that is close (and much shorter than what I had.)
This is what I entered as a formula

Please Login or Register  to view this content.

Many of the cells are empty on my sheet and many of those are being highlighted yellow as well as a few cells with letters entered with no entry to the left.

[6StringJazzer]

Sorry, if C5 is blank then it will return a 1. Try this:

Please Login or Register  to view this content.

[Phuocam]

Or try:

=(LEN(B5)=1)*(LEN(C5)=1)*FIND(B5,"ATPUHEN")*FIND(C5,"ZDF")

[sc204]

Sorry, if C5 is blank then it will return a 1. Try this:

Please Login or Register  to view this content.

This worked better most of the blank cells that were formatting were fine but any blank cell before a D, Z, F, B, G or Q would format yellow.

The formula from Phuocam did work though so all is good. I really appreciate the help.

[sc204]

Or try:

=(LEN(B5)=1)*(LEN(C5)=1)*FIND(B5,"ATPUHEN")*FIND(C5,"ZDF")

This worked
If I am reading what you did correctly all 4 criteria have to be fulfilled (1) to format. The LEN function makes sure something is entered in both cells and the find looks for any of my letters. Any one not present gives 0 and no format.

Very elegant. Thank you

[Phuocam]

You're welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[sc204]

I had a couple of hiccups as my lettering system is not as straight forward as the original example (the letters are shifts on a schedule which is why morning shifts can't follow evening or overnight shifts for the same person)
I also have a couple of 2 letter shifts N2 and F2. F2 can't be followed by any of the morning shifts including F, and N2 can't be followed by any shifts except N, M, and N2.
Morning shifts D, Z, G, B, F Q: Evening shifts A, U, T, P E, H: Night shifts N, M, N2: afternoon through evening shift F2

I ended up using 3 conditional formatting statements based on Phuocam's suggestion.

Since N2 is a night shift I modified the original formula to

Please Login or Register  to view this content.

To cover night shifts not being followed by evening shifts either

Please Login or Register  to view this content.

And to cover the shifts that can't follow F2 which includes F

Please Login or Register  to view this content.

Not sure if there is anyway to combine these statements.
I tried using an array but got an error that it couldn't be used in CF.

Thanks again,
Stuart