SOLVED
Home

Conditional Formatting with Formulas - Help PLease?! :)

%3CLINGO-SUB%20id%3D%22lingo-sub-319323%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20with%20Formulas%20-%20Help%20PLease%3F!%20%3A)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319323%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20insert%20a%20forumula%20as%20part%20of%20conditional%20formatting%20into%20a%20tracker%20on%20a%20spreadsheet%20to%20highlight%20expired%20dates%20but%20only%20when%20text%20in%20another%20column%20meets%20certain%20criteria.%26nbsp%3B%3C%2FP%3E%3CP%3E(E.g.%20If%20the%20date%20in%20column%20B%20occurs%20in%20the%20past%20%3CEM%3E%3CSTRONG%3Eand%3C%2FSTRONG%3E%20%3C%2FEM%3Ethe%20text%20in%20column%20F%20is%20equal%20to%20%22PENDING%22%20or%20%22IN%20QUERY%22%20-%20if%20column%20F%20contains%20any%20other%20content%20i%20do%20not%20want%20the%20formatting%20applied)%3C%2FP%3E%3CP%3EI%20know%20how%20to%20do%20each%20of%20these%20things%20seperately%20but%20for%20the%20life%20of%20me%20cannot%20work%20out%20how%20to%20stitch%20the%202%20formula%20together%20to%20work%20as%20one%20(i%20thought%20i%20could%20use%20an%20IF%20formula%20to%20calculate%20but%20cannot%20get%20it%20to%20work).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20really%20appreicate%20any%20advice%20on%20how%20to%20make%20this%20work.%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3CP%3EJen%20%3A)%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-319323%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EConditional%20Formatting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-324917%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20with%20Formulas%20-%20Help%20PLease%3F!%20%3A)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-324917%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%2C%26nbsp%3B%20would%20you%20mind%20posting%20this%20as%20a%20separate%20question%20to%20the%20forum%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-324544%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20with%20Formulas%20-%20Help%20PLease%3F!%20%3A)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-324544%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%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20there!%20I'm%20having%20a%20similar%20problem.%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-SUB%20id%3D%22lingo-sub-319416%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20with%20Formulas%20-%20Help%20PLease%3F!%20%3A)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319416%22%20slang%3D%22en-US%22%3EHi%20Wyn%2C%3CBR%20%2F%3E%3CBR%20%2F%3EIt's%20worked!%20Thank%20you%20so%20much.%20I%20really%20apprecaite%20you%20taking%20the%20time%20to%20help%20%3A)%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%3CBR%20%2F%3EJen%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-319412%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20with%20Formulas%20-%20Help%20PLease%3F!%20%3A)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319412%22%20slang%3D%22en-US%22%3EI%20would%20highlight%20B2%20to%20B200%20and%20then%20apply%20the%20following%20formula%3CBR%20%2F%3E%3CBR%20%2F%3E%3DAND(%20B2%3CTODAY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-319398%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20with%20Formulas%20-%20Help%20PLease%3F!%20%3A)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319398%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOh%20great%2C%20thank%20you.%20That%20will%20make%20a%20really%20useful%20addition!%20%3A)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%26nbsp%3B%3CBR%20%2F%3EJen%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-319397%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20with%20Formulas%20-%20Help%20PLease%3F!%20%3A)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319397%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Wyn%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI've%20added%20a%20snapshot%20of%20my%20spreadsheet%20below.%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20goal%20is%20for%20dates%20in%20column%20B%20(INV%20DUE%20DATE)%20to%20be%20highlighted%20red%20but%20only%20if%20the%20answer%20in%20column%20F%20(STATUS)%20reads%20PENDING%20or%20IN%20QUERY.%20If%20column%20F%20reads%20PAID%20I%20do%20not%20want%20the%20formatting%20added%20to%20B.%20I%20hope%20that%20makes%20sense%3F%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20adding%20the%20conditional%20formatting%20to%20columb%20B%2C%20i%20start%20by%20selecting%20the%20whole%20column%2C%20then%26nbsp%3Bopening%20the%20conditional%20formatting%20options%20to%20apply%20the%20formula%26nbsp%3B%20-%20is%20this%20correct%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20if%20i'm%20not%20explaining%20this%20well%2C%20i'm%20not%20the%20best%20with%20formulas%26nbsp%3Bcontaining%20more%20than%20one%20function!%20haha%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20really%20appreicate%20your%20help.%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EJen%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20709px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68776i5A8AB6C8DF6AF15F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22ExcelCapture.JPG%22%20title%3D%22ExcelCapture.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-319386%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20with%20Formulas%20-%20Help%20PLease%3F!%20%3A)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319386%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20excludes%20blank%20cells%20in%20column%20B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-319381%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20with%20Formulas%20-%20Help%20PLease%3F!%20%3A)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319381%22%20slang%3D%22en-US%22%3E%3CBR%20%2F%3EHi%3CBR%20%2F%3E%3CBR%20%2F%3ECan%20you%20attach%20a%20screenshot%20of%20what%20you%20mean%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-319372%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20with%20Formulas%20-%20Help%20PLease%3F!%20%3A)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319372%22%20slang%3D%22en-US%22%3EHi%20Sergei%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20for%20your%20input%20-%20you'll%20have%20to%20excuse%20my%20very%20basic%20knowledge%20of%20excel%20formulas%2C%20but%20what%20result%20would%20this%20formula%20acheive%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20you%3CBR%20%2F%3EJen%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-319368%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20with%20Formulas%20-%20Help%20PLease%3F!%20%3A)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319368%22%20slang%3D%22en-US%22%3E%3CP%3EPerhaps%20to%20expand%3C%2FP%3E%0A%3CPRE%3E(B1%26lt%3BTODAY())*(B1%26gt%3B0)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-319365%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20with%20Formulas%20-%20Help%20PLease%3F!%20%3A)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319365%22%20slang%3D%22en-US%22%3EHi%20Wyn%2C%3CBR%20%2F%3EThank%20you!%20I%20have%20just%20tried%20this%20and%20it%20seems%20to%20be%20looking%20at%20both%20columns%20for%20the%20answer%20now%20which%20is%20great%20but%20instead%20of%20highlighting%20just%20cell%20'B1'%20(if%20PENDING%20or%20IN%20QUERY%20is%20present%20in%20F1)%20it%20is%20highlighting%20the%20whole%20of%20column%20B.%20Any%20ideas%20of%20how%20to%20adjust%20so%20that%20it%20only%20highlights%20the%20cell%3F%3CBR%20%2F%3EThanks%20again%3CBR%20%2F%3EJen%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-319358%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20with%20Formulas%20-%20Help%20PLease%3F!%20%3A)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319358%22%20slang%3D%22en-US%22%3ETry%20this%20%20%20(no%20need%20for%20the%20IF%20in%20conditional%20formatting)%3CBR%20%2F%3E%3CBR%20%2F%3E%3DAND(%20%20B1%3CTODAY%3E%3C%2FTODAY%3E%3C%2FLINGO-BODY%3E%3C%2FTODAY%3E%3C%2FLINGO-BODY%3E
JenSmith
Occasional Contributor

Hi All, 

 

I am trying to insert a forumula as part of conditional formatting into a tracker on a spreadsheet to highlight expired dates but only when text in another column meets certain criteria. 

(E.g. If the date in column B occurs in the past and the text in column F is equal to "PENDING" or "IN QUERY" - if column F contains any other content i do not want the formatting applied)

I know how to do each of these things seperately but for the life of me cannot work out how to stitch the 2 formula together to work as one (i thought i could use an IF formula to calculate but cannot get it to work). 

 

I would really appreicate any advice on how to make this work. 

Thanks in advance

Jen :) 

12 Replies
Try this (no need for the IF in conditional formatting)

=AND( B1<TODAY(), OR(F1="PENDING", F1="IN QUERY") )
Hi Wyn,
Thank you! I have just tried this and it seems to be looking at both columns for the answer now which is great but instead of highlighting just cell 'B1' (if PENDING or IN QUERY is present in F1) it is highlighting the whole of column B. Any ideas of how to adjust so that it only highlights the cell?
Thanks again
Jen

Perhaps to expand

(B1<TODAY())*(B1>0)
Hi Sergei,

Thanks for your input - you'll have to excuse my very basic knowledge of excel formulas, but what result would this formula acheive?

Thanks you
Jen

Hi

Can you attach a screenshot of what you mean?


It excludes blank cells in column B

Hi Wyn, 


I've added a snapshot of my spreadsheet below. 

My goal is for dates in column B (INV DUE DATE) to be highlighted red but only if the answer in column F (STATUS) reads PENDING or IN QUERY. If column F reads PAID I do not want the formatting added to B. I hope that makes sense? 

When adding the conditional formatting to columb B, i start by selecting the whole column, then opening the conditional formatting options to apply the formula  - is this correct? 

 

Sorry if i'm not explaining this well, i'm not the best with formulas containing more than one function! haha 

 

I really appreicate your help. 

Thanks

Jen 

 

ExcelCapture.JPG

 

Hi Sergei, 

 

Oh great, thank you. That will make a really useful addition! :) 

 

Thanks, 
Jen

Solution
I would highlight B2 to B200 and then apply the following formula

=AND( B2<TODAY(), OR(F2="PENDING", F2="IN QUERY") )
Hi Wyn,

It's worked! Thank you so much. I really apprecaite you taking the time to help :)

Thanks
Jen

@Wyn Hopkins

@Sergei Baklan

 

Hi there! I'm having a similar problem. 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.

Hi @jfh117 ,  would you mind posting this as a separate question to the forum?

 

Thanks

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies