Conditional formatting using variable texts in range of cells on same or other worksheet

[r.coon]

I use a spreadsheet of over 300 rows to track my wife's driving miles for Business, Medical, Charity & other things. When recording business miles we track the client and if it is associated with a "PU" or "DO" we also record the Start & Stop times to track hours (haven't got to extracting each clients total hours yet...:-).

As you can see in my example some of the client activity overlaps others so it is hard to visually track which Start & Stop times go together. I used Conditional Formatting to automatically select a font color related to a specific clients name and apply that format to the whole row whenever that clients name appears in conjunction with either a "PU" or a "DO" activity. This all works great except the client names are not stable. Some are repeat clients, some irregular and some new come and go. It is a bit of a hassle to keep redoing the CF rules and formulas every time I run into a new name.

For this reason I would like to have the CF rule refer to cells where I would list the names - say 10 cells, each preassigned to a specific color in the CF rules, then if a new name comes up I just enter it in the "CF trigger" cells and it will automatically trigger the assigned formatting whenever that name appears in conjunction with the "PU" or "DO" activity.

I'm sure I've seen a slick way to do this on Excel Forum but it was given as an additional thing to try in relation to another issue so now I can't find it.

TIA

[newdoverman]

I think that you would be further ahead using Conditional Formatting on the WHAT column codes. If you test what you have, if the name is missing the WHAT codes do not format with colour as every rule has a different rule for the code. Have a different colour for each of the WHAT codes. The OR part of all your rules are doing nothing as it now stands.

I would simplify the form a bit. I deleted 4 or 5 columns (Bus, Jam, Char etc) and made a single column and used Bus, Jm, Char instead of an "X".

A summary (just an example of what can be done) was created with a Pivot Table.

[r.coon]

Your pivot table is certainly compact, though I am not sophisticated enough as an excel user to understand how it improves usefulness for this application.

The form I created (actually, it evolved over time) was developed to make data entry straightforward with fewest keystrokes for quick entry (hence using "tab" & "X" instead of typing Bus, Med, Char, etc). It's very time consuming to enter 300+ rows of data (sometimes by non-computer comfy people); auto-fill kicks in to help out, but it seems kinda fickle at times.

The sample form modifications you provided do not meet my main objective listed in OP, "...I would like to have the CF (conditional format) rule refer to cells where I would list the names...and it will automatically trigger the assigned formatting whenever that name appears [only] in conjunction with the "PU" or "DO" activity".

Although the formula you use is simpler in the example you offer, it still requires names to be directly entered into each CF formula and now the CF rule is color formatting rows that do not use "DO" or "PU" (e.g. row 16 has "Therapy" in G16 but row is green anyway).

Sorry, it is clear I failed to explain my goal accurately and probably am using incorrect terminology.

My original example, works fine except I want to eliminate entering "Matthew" (or any name) in the CF formula; rather I want to, for example, enter "Matthew" in spreadsheet "CF trigger" cell Q7 and have the CF formula refer to Q7 (trigger) and G4 ("WHAT" - PU, DO, Therapy, etc) when evaluating F4 (name listed under "WHO") - If the "WHAT" is a PU or DO and the name in "WHO" matches the trigger name listed in the CF trigger cell, then the color formatting rule that refers to that trigger cell will be applied to entire row.

I will attach another sample with arrows showing desired "precedents".

[newdoverman]

Ok, here is your file with CF that is applied to names that are entered in U7 to U16. The cell address replaces the name in the CF formula. If you go past U16, you will have to create new rules that address the additional cells. I assigned colours to the cell references but you can change them to suit yourself.

The CF will change the formatting if and only if the name is in the list in column U and column K has "DO" or "PU". Otherwise, the font will be black.

[r.coon]

Thanks Newdoverman! Now I see what I was missing...

I had tried similar earlier but having removed all of the "$" from the row number references in the formula to make sure the CF would work anywhere on the sheet as I "painted" formatting down to future rows I forgot the "trigger" cell row reference still requires a "$" to specify that exact cell no matter where the formatting happens on the spreadsheet so obviously (now) it didn't work before.

Question: What does "Stop If True" box for each rule do? I see you unchecked them - when I played with it I didn't see how spreadsheet behavior changed but don't know what to l

For any who might read this thread and wonder what the answer to original question was in a nutshell:

Under Conditional Formatting > Manage Rules > Edit Rule are a list of rules for each format choice I want to "trigger".
The formula used to describe the event: =AND($J3=$U$16,OR($K3="DO",$K3="PU"))

=AND($J3....OR($K3
Anywhere on spreadsheet (starting from row 3) CF is looking for 2 trigger events

1st trigger: $J3=$U$16
If contents of cell in column J for any given row equals contents of U16 only
(I can use any number of these "trigger" cells up to maximum rules allowed as long as I refer to column AND row with "$" to specify an exact cell position not a relative position)

2nd trigger: OR($K3="DO",$K3="PU")
And if content of cell in column K (for the same row as 1st trigger) equals either DO or PU

Then chosen format for this rule will be triggered and applied to =$A$3:$S$32
After writing the initial rule on row 3 I can "paint" formatting to other rows and the area affected will always be A3:S32

[newdoverman]

Thanks Newdoverman! Now I see what I was missing...

Question: What does "Stop If True" box for each rule do? I see you unchecked them - when I played with it I didn't see how spreadsheet behavior changed but don't know what to l

Sometimes, the order of the rules makes a big difference and a STOP will not process more rules if there are rules that interfere with the rule with the STOP. If all the rules are different and don't have the possibility of interfering with each other then the STOP isn't necessary.

If you feel adventurous, you could create Data Validation drop down lists for the columns where you put "X"s and where you enter names. Drop down lists for names is especially important if you want to maintain the integrity of the data entry. Having consistent exact entries is very important. The other bit with the columns of "X"s being replaced with the equivalent column header allows for the use of Pivot tables and formulae to summarize your data quickly and easily. Maybe later that could be something to think about adding.

After writing the initial rule on row 3 I can "paint" formatting to other rows and the area affected will always be A3:S32

If I use the "paint" method, I always to back to the Conditional Formatting, Manage Rules to be sure that I am not creating many ranges that can be contained in one contiguous range. This makes it easier to read of gremlins creep into the picture.

Thank you for the feedback and rep.

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

[r.coon]

More good things to consider, thank you again for the added insight.