SOLVED

@Excel @2016 @IF Function; help removing conditional formatting until cell has data

%3CLINGO-SUB%20id%3D%22lingo-sub-3035957%22%20slang%3D%22en-US%22%3E%40Excel%20%402016%20%40IF%20Function%3B%20help%20removing%20conditional%20formatting%20until%20cell%20has%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3035957%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20very%20new%20at%20Excel%20formulas%2C%20learning%20as%20I%20go.%3C%2FP%3E%3CP%3EI%20am%20attempting%20to%20include%20an%20IF%20Function%20that%20will%20only%20include%20data%20when%20other%20cells%20have%20data%20(see%20attached).%26nbsp%3B%20I%20have%20figured%20it%20out%2C%20however%20the%20formatting%20that%20applies%20when%20a%20file's%20age%20is%20greater%20than%2030%20appears.%26nbsp%3B%20I%20would%20like%20to%20have%20the%20formula%20in%20the%20cell%20without%20the%20conditional%20formatting%20UNTIL%20data%20is%20entered%20in%20the%20adjacent%20cells.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20issue%20is%20with%20Column%20D%20where%20the%20formula%20is%3B%20where%20if%20C35%20is%20bland%2C%20then%20it%20will%20remain%20blank.%26nbsp%3B%20Otherwise%2C%20it%20will%20do%20one%20of%20two%20things%2C%20either%20calculate%20the%20net%20working%20days%20between%20date%20of%20receipt%20(E)%20and%20Today's%20date%20(F)%20%3CSTRONG%3Eor%3C%2FSTRONG%3E%20net%20working%20days%20between%20Date%20of%20receipt%20(E)and%20Date%20of%20decision%20(Z)%20%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(C35%3D%22%22%2C%22%22%2CIF(Z35%3D%22%22%2CNETWORKDAYS(E35%2CF35)%2CNETWORKDAYS(E35%2CZ35)))%3C%2FP%3E%3CP%3EThe%20conditional%20formatting%20applicable%20to%20column%20D%20provides%20for%20pink%20background%20and%20wine%20colored%20font%2C%20is%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3EFormat%20only%20cells%20that%20contain%3C%2FSTRONG%3E%2C%20Cell%20Value%2C%20Greater%20than%2C%20%3D30%3C%2FP%3E%3CP%3EI%20have%20attempted%20some%20suggestions%20from%20online%20forums%20unsuccessfully.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3Eex.%3A%20%3CSTRONG%3EUse%20a%20formula%20to%20determine%20which%20cells%20to%20format%3B%3C%2FSTRONG%3E%26nbsp%3B%3D%20ISBLANK(D2)%3DTRUE%2C%20with%20no%20Format%20Set%2C%20applicable%20from%20D2%3AD50%3C%2FP%3E%3CP%3ETried%20these%20as%20well%2C%20unsuccessfully%20%3A%3C%2FP%3E%3CP%3E%3DIF(Z2%3DISBLANK(D2)%2CNETWORKDAYS(E34%2CF34)%2CNETWORKDAYS(E34%2CZ34))%3C%2FP%3E%3CP%3E%3DIF(Z34%3DISBLANK(Z34)%2CNETWORKDAYS(E34%2CF34)%2CNETWORKDAYS(E34%2CZ34))%3C%2FP%3E%3CP%3E%3DIF((C34%3D%22%22%2C%22%22%2CTODAY())(Z34%3DISBLANK(Z34)%2CNETWORKDAYS(E34%2CF34)%2CNETWORKDAYS(E34%2CZ34)))%3C%2FP%3E%3CP%3E%3DIFS(C34%3D%22%22%2C%22%22%2CNETWORKDAYS(E34%2CF34))(Z34%3D%22%22%2CNETWORKDAYS(E34%2CF34)%2CNETWORKDAYS(E34%2CZ34))%3C%2FP%3E%3CP%3EAppreciate%20your%20support%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3035957%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3036025%22%20slang%3D%22en-US%22%3ERe%3A%20%40Excel%20%402016%20%40IF%20Function%3B%20help%20removing%20conditional%20formatting%20until%20cell%20has%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3036025%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1098908%22%20target%3D%22_blank%22%3E%40Wendy2291%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DAND(D35%26gt%3B30%2CC35%26lt%3B%26gt%3B%22%22)%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20i%20correctly%20understand%20what%20you%20want%20to%20do%20you%20can%20apply%20above%20rule%20for%20conditional%20formatting.%20I%20deleted%20all%20rules%20for%20conditional%20formatting%20in%20the%20original%20file%20(some%20of%20these%20rules%20are%20for%20column%20D%20as%20well)%20and%20focused%20on%20a%20possible%20solution%20and%20it%20works%20in%20my%20spreadsheet%20for%20range%20D35%3AD50.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am very new at Excel formulas, learning as I go.

I am attempting to include an IF Function that will only include data when other cells have data (see attached).  I have figured it out, however the formatting that applies when a file's age is greater than 30 appears.  I would like to have the formula in the cell without the conditional formatting UNTIL data is entered in the adjacent cells.  

My issue is with Column D where the formula is; where if C35 is bland, then it will remain blank.  Otherwise, it will do one of two things, either calculate the net working days between date of receipt (E) and Today's date (F) or net working days between Date of receipt (E)and Date of decision (Z) : 

=IF(C35="","",IF(Z35="",NETWORKDAYS(E35,F35),NETWORKDAYS(E35,Z35)))

The conditional formatting applicable to column D provides for pink background and wine colored font, is:

Format only cells that contain, Cell Value, Greater than, =30

I have attempted some suggestions from online forums unsuccessfully.  

ex.: Use a formula to determine which cells to format; = ISBLANK(D2)=TRUE, with no Format Set, applicable from D2:D50

Tried these as well, unsuccessfully :

=IF(Z2=ISBLANK(D2),NETWORKDAYS(E34,F34),NETWORKDAYS(E34,Z34))

=IF(Z34=ISBLANK(Z34),NETWORKDAYS(E34,F34),NETWORKDAYS(E34,Z34))

=IF((C34="","",TODAY())(Z34=ISBLANK(Z34),NETWORKDAYS(E34,F34),NETWORKDAYS(E34,Z34)))

=IFS(C34="","",NETWORKDAYS(E34,F34))(Z34="",NETWORKDAYS(E34,F34),NETWORKDAYS(E34,Z34))

Appreciate your support

 

1 Reply
best response confirmed by Wendy2291 (New Contributor)
Solution

@Wendy2291 

=AND(D35>30,C35<>"")  

 

If i correctly understand what you want to do you can apply above rule for conditional formatting. I deleted all rules for conditional formatting in the original file (some of these rules are for column D as well) and focused on a possible solution and it works in my spreadsheet for range D35:D50.