SOLVED
Home

Conditional Formatting Formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-324926%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-324926%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3E%40Wyn%20Hopkins%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3E%40Sergei%20Baklan%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20there!%20I%20have%20a%20list%20of%20personnel%20who%20each%20have%20one%20of%20a%20few%20different%20levels%20of%20training.%20Their%20names%20are%20listed%20in%201%20column%20and%20their%20level%20in%20another.%20Then%20I%20have%20another%20table%20on%20a%20different%20sheet%20with%20all%20of%20their%20names%20under%20the%20positions%20where%20they%20work%2C%20and%20they%20may%20be%20in%20multiple%20positions.%20I%20would%20like%20the%20names%20in%20the%20position%20table%20to%20turn%20either%20green%2C%20yellow%2C%20or%20red%20based%20on%20the%20level%20of%20training%20they've%20had%20as%20listed%20on%20the%20other%20worksheet%20with%20the%20two%20columns.%20For%20example%2C%20when%20Bob%20Smith%20has%20had%20ICS%20300%20training%2C%20I%20want%20every%20cell%20with%20his%20name%20to%20turn%20green%20in%20the%20big%20table.%20How%20do%20I%20go%20about%20doing%20this.%20I%20can%20get%20the%20color%20change%20when%20I%20stay%20within%20the%20two%20columns%20by%20doing%20%3D%24B2%3D300%2C%20but%20I%20cannot%20do%20it%20across%20worksheets.%20Even%20when%20I%20moved%20the%20two%20columns%20to%20the%20same%20sheet%20as%20the%20big%20table%2C%20I%20could%20not%20figure%20out%20a%20formula%20that%20highlighted%20the%20cells%20I%20wanted.%20It%20would%20highlight%20select%20ones%20randomly%2C%20or%20go%20across%20a%20few%20rows%2C%20but%20the%20level%20of%20training%20and%20the%20names%20did%20not%20correlate%20correctly.%20Thank%20you%20for%20you%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-324926%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EConditional%20Formatting%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-325938%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-325938%22%20slang%3D%22en-US%22%3EAh%20yes%2C%20best%20to%20apply%20data%20validation%20to%20the%20Tables%20based%20on%20the%20list.%20Glad%20you%20got%20it%20working%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-325747%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-325747%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20figured%20out%20that%20I%20had%20spaces%20next%20to%20some%20of%20the%20names%20in%20the%20table%20and%20not%20in%20the%20same%20ones%20in%20the%20column.%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-325342%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-325342%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you're%20able%20to%20attach%20a%20sample%20file%20I%20can%20take%20a%20look.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMost%20common%20mistake%20I%20find%20when%20users%20have%20formulas%20in%20conditional%20formatting%20is%20the%20%24%20signs%20get%20mixed%20up%2C%26nbsp%3B%20or%20the%20wrong%20cells%20are%20highlighted%20when%20writing%20the%20formula.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ee.g.%20highlight%20all%20cells%20that%20you%20want%20the%20conditional%20formatting%20to%20apply%20to%20before%20writing%20the%20conditional%20format%20formula%20and%20make%20sure%20that%20formula%20is%20relevant%20to%20the%20top%20left%20corner%20cell%20that%20is%20highlighted.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20after%20applying%20the%20formatting%20click%20on%20a%20different%20cell%20and%20check%20the%20formula%20that%20is%20being%20applied%20to%20it%20via%20Conditional%20Formatting%20%26gt%3B%20Manage%20rules.%26nbsp%3B%20%26nbsp%3B%20If%20the%20formula%20isn't%20referring%20to%20the%20correct%20cell%20then%20start%20over%20and%20change%20your%20%24%20signs.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHaving%20said%20all%20this%20-%20it%20could%20be%20a%20totally%20different%20reason%20%3A)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-325007%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-325007%22%20slang%3D%22en-US%22%3E%3CP%3EUpdate%3A%20I%20can%20get%20some%20of%20the%20names%20to%20change%20color%20correctly%2C%20but%20not%20all%20of%20them%20become%20colored%20and%20I'm%20not%20sure%20why.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-325002%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-325002%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%3C%2FP%3E%3CP%3EI'm%20trying%20to%20use%20the%20formulas%20that%20you%20did%20in%20relation%20to%20my%20data%2C%20but%20nothing%20changes%20color.%20I'm%20not%20sure%26nbsp%3Bwhat%20I%20should%20do%20differently.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-324945%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-324945%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%20exactly!%20How%20did%20you%20do%20that%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-324941%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-324941%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F270759%22%20target%3D%22_blank%22%3E%40jfh117%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIs%20the%20attached%20file%20the%20sort%20of%20thing%20you%20are%20looking%20for%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-558629%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-558629%22%20slang%3D%22en-US%22%3ECan%20a%20conditional%20format%20be%20replaced%20with%20a%20if%2C%20then%20function%20containing%20format%20functions%3F%20If%20so%2C%20where%20can%20I%20get%20information%20and%20examples%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-562255%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-562255%22%20slang%3D%22en-US%22%3EI'm%20not%20clear%20on%20what%20you%20mean%20sorry%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-574988%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-574988%22%20slang%3D%22en-US%22%3EInstead%20of%20using%20cell%20Conditional%20formatting%20to%20set%20a%20color%20based%20on%20cell%20value%2C%20can%20this%20be%20done%20with%20an%20equation%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-575031%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-575031%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F257912%22%20target%3D%22_blank%22%3E%40J2-____jj%3C%2FA%3E%26nbsp%3B%2C%20nope%2C%20not%20with%20formulas%3C%2FP%3E%3C%2FLINGO-BODY%3E
jfh117
Occasional Contributor

@Wyn Hopkins

@Sergei Baklan

 

Hi there! I have a list of personnel who each have one of a few different levels of training. Their names are listed in 1 column and their level in another. Then I have another table on a different sheet with all of their names under the positions where they work, and they may be in multiple positions. I would like the names in the position table to turn either green, yellow, or red based on the level of training they've had as listed on the other worksheet with the two columns. For example, when Bob Smith has had ICS 300 training, I want every cell with his name to turn green in the big table. How do I go about doing this. I can get the color change when I stay within the two columns by doing =$B2=300, but I cannot do it across worksheets. Even when I moved the two columns to the same sheet as the big table, I could not figure out a formula that highlighted the cells I wanted. It would highlight select ones randomly, or go across a few rows, but the level of training and the names did not correlate correctly. Thank you for you help.

11 Replies

Hi @jfh117 

 

Is the attached file the sort of thing you are looking for?

@Wyn Hopkins

 

Yes exactly! How did you do that?

@Wyn Hopkins

I'm trying to use the formulas that you did in relation to my data, but nothing changes color. I'm not sure what I should do differently.

Update: I can get some of the names to change color correctly, but not all of them become colored and I'm not sure why.

Solution

Hi

 

If you're able to attach a sample file I can take a look.

 

Most common mistake I find when users have formulas in conditional formatting is the $ signs get mixed up,  or the wrong cells are highlighted when writing the formula.

 

e.g. highlight all cells that you want the conditional formatting to apply to before writing the conditional format formula and make sure that formula is relevant to the top left corner cell that is highlighted.

 

Then after applying the formatting click on a different cell and check the formula that is being applied to it via Conditional Formatting > Manage rules.    If the formula isn't referring to the correct cell then start over and change your $ signs.

 

Having said all this - it could be a totally different reason :)

 

So I figured out that I had spaces next to some of the names in the table and not in the same ones in the column. Thank you!

Ah yes, best to apply data validation to the Tables based on the list. Glad you got it working
Can a conditional format be replaced with a if, then function containing format functions? If so, where can I get information and examples?
I'm not clear on what you mean sorry
Instead of using cell Conditional formatting to set a color based on cell value, can this be done with an equation?

@J2-____jj , nope, not with formulas

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies