SOLVED

Conditional Formatting Issue on Table Office 365

%3CLINGO-SUB%20id%3D%22lingo-sub-1543005%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20Issue%20on%20Table%20Office%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1543005%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20this%20formula%20for%20conditional%20formatting%20on%20a%20particular%20column%20in%20a%20table%20but%20whenever%20I%20try%20to%20paste%20the%20formula%20into%20the%20conditional%20formatting%20rule%20it%20gives%20me%20the%20error%20saying%26nbsp%3B%20%22There's%20a%20problem%20with%20this%20formula.%20Not%20trying%20to%20type%20a%20formula%3F...%22%20and%20then%20comments%20out%20my%20formula.%20The%20formula%20works%20fine%20in%20any%20other%20cell%20in%20excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20formula%20I%20am%20trying%20to%20use.%3C%2FP%3E%3CP%3E%3DAND(Table1%5B%40%5BBatch%20Lot%5D%5D%3D%22%22%2CTable1%5B%40%5BDelivery%20Date%5D%5D-TODAY()%26lt%3B14)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20file%20is%20to%20keep%20track%20of%20orders%20and%20samples%20of%20ingredients.%20So%20what%20I%20am%20trying%20to%20do%20with%20this%20formula%20is%20that%20if%20the%20batch%20lot%20field%20is%20blank%20(meaning%20we%20haven't%20gotten%20a%20sample)%20and%20the%20delivery%20date%20of%20the%20order%20is%20less%20than%2014%20days%20away%20then%20the%20statement%20is%20true%20and%20I%20want%20it%20to%20highlight%20the%20cell%20red.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20tried%20selecting%20the%20cells%20for%20those%20columns%20instead%20of%20the%20names%2C%20but%20then%20it%20doesn't%20actually%20work%20properly%20and%20give's%20incorrect%20true%2Ffalse.%3C%2FP%3E%3CP%3E%3DAND(T2%3D%22%22%2CG2-TODAY()%26lt%3B14)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20tried%20making%20a%20column%20off%20to%20the%20side%20with%20the%20first%20formula%20and%20it%20gives%20a%20correct%20True%2FFalse%20and%20then%20using%20a%20conditional%20formatting%20that%20just%20references%20that%20cell%2C%20but%20still%20gives%20back%20incorrect%20formatting%2C%20like%20my%20example%20above.%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%20image-alt%3D%22Formating%201.JPG%22%20style%3D%22width%3A%20223px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F207590i56962FEBCAC4F174%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Formating%201.JPG%22%20alt%3D%22Formating%201.JPG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Formating%202.JPG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F207591iE0C5178169BECA58%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Formating%202.JPG%22%20alt%3D%22Formating%202.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1543005%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1543041%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Issue%20on%20Table%20Office%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1543041%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F738489%22%20target%3D%22_blank%22%3E%40Derek-Ude%3C%2FA%3E%26nbsp%3BHaven't%20tried%20with%20table%20references%2C%20but%20in%20your%20second%20example%20you%20are%20trying%20to%20match%20cells%20from%20row%202%20and%20down%20to%20values%20in%20an%20entire%20column.%20This%20will%20cause%20the%20conditional%20format%20to%20be%20offset%20by%20one%20row%2C%20as%20shown%20in%20your%20picture.%20Change%20%22Y2%22%20to%20%22Y1%22%20or%20set%20the%20range%20to%20e.g.%20%22G2%3AG10000%22.%20Then%20it%20should%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1543291%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Issue%20on%20Table%20Office%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1543291%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F738489%22%20target%3D%22_blank%22%3E%40Derek-Ude%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EConditional%20formatting%20doesn't%20work%20with%20structured%20references%20directly%20The%20workaround%20is%20to%20use%20INDIRECT()%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DAND(INDIRECT(%22Table1%5B%40%5BBatch%20Lot%5D%5D%22)%3D%22%22%2CINDIRECT(%22Table1%5B%40%5BDelivery%20Date%5D%5D%22)-TODAY()%26lt%3B14)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1544015%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Issue%20on%20Table%20Office%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1544015%22%20slang%3D%22en-US%22%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%26nbsp%3BThank%20you%2C%20that%20worked%20perfectly!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1544313%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Issue%20on%20Table%20Office%20365%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1544313%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F738489%22%20target%3D%22_blank%22%3E%40Derek-Ude%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello, 

 

I am trying to use this formula for conditional formatting on a particular column in a table but whenever I try to paste the formula into the conditional formatting rule it gives me the error saying  "There's a problem with this formula. Not trying to type a formula?..." and then comments out my formula. The formula works fine in any other cell in excel.

 

This is the formula I am trying to use.

=AND(Table1[@[Batch Lot]]="",Table1[@[Delivery Date]]-TODAY()<14)

 

This file is to keep track of orders and samples of ingredients. So what I am trying to do with this formula is that if the batch lot field is blank (meaning we haven't gotten a sample) and the delivery date of the order is less than 14 days away then the statement is true and I want it to highlight the cell red.

 

I also tried selecting the cells for those columns instead of the names, but then it doesn't actually work properly and give's incorrect true/false.

=AND(T2="",G2-TODAY()<14)

 

I also tried making a column off to the side with the first formula and it gives a correct True/False and then using a conditional formatting that just references that cell, but still gives back incorrect formatting, like my example above.

 

Formating 1.JPGFormating 2.JPG

4 Replies
Highlighted

@Derek-Ude Haven't tried with table references, but in your second example you are trying to match cells from row 2 and down to values in an entire column. This will cause the conditional format to be offset by one row, as shown in your picture. Change "Y2" to "Y1" or set the range to e.g. "G2:G10000". Then it should work.

Highlighted
Best Response confirmed by Derek-Ude (New Contributor)
Solution

@Derek-Ude 

Conditional formatting doesn't work with structured references directly The workaround is to use INDIRECT() like

=AND(INDIRECT("Table1[@[Batch Lot]]")="",INDIRECT("Table1[@[Delivery Date]]")-TODAY()<14)
Highlighted

@Sergei Baklan Thank you, that worked perfectly!

Highlighted

@Derek-Ude , you are welcome