Jul 23 2020 09:28 PM
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.
Jul 23 2020 10:18 PM
@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.
Jul 24 2020 01:41 AM
SolutionConditional 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)
Jul 24 2020 08:59 AM
@Sergei Baklan Thank you, that worked perfectly!
Jul 24 2020 11:07 AM
@Derek-Ude , you are welcome
Jul 24 2020 01:41 AM
SolutionConditional 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)