SOLVED

Conditional Formatting Issue on Table Office 365

Copper 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

@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.

best response confirmed by Derek-Ude (Copper 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)

@Sergei Baklan Thank you, that worked perfectly!

@Derek-Ude , you are welcome

1 best response

Accepted Solutions
best response confirmed by Derek-Ude (Copper 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)

View solution in original post