Forum Discussion
Conditional Formatting does not recognize formula
I'm trying to use a formula to conditionally format and I keep getting the error message "There's a problem with this formula." I want to highlight projects in column A if they are less than 7 days old. I created a formula (LAMDA) that correctly calculates the number of days on the project (column B) and I used the same formula <7 to generate TRUE/FALSE results (column C).
I can conditionally format columns B and C and I can conditionally format column A if I refer to either column B or C. I cannot use either the B column or C column formula in a conditional format directly on $A31.
I've verified the following:
* the Formula checker found no errors on the worksheet;
* my references are correct when I've pasted the formulas into Conditional Formatting formula bar ($A31 is the active cell and the formula is the same as the formula on the worksheet);
* Abiola David noted an apparent bug in using LAMDA calculations in conditional formatting in his YouTube channel but this doesn't seem to be related to my issue. I've created an XLOOKUP formula that gets the same results and get the same error message.
3 Replies
Which exactly formula do you try to use?
- Per the screenshot in my previous post:
Column A is text.
Column B formula =AuditFormat($A31,Oversight[Date Assigned]), where
AuditFormat is a named formula as follows:
LAMDA(auditID,PhaseDate,DAYS(TODAY(),XLOOKUP(auditID,Oversight[Audit No.],PhaseDate)))
Column C formula =AuditFormat($A31,Oversight[Date Assigned])<7
Both column B and C are referencing the same project IDs and the parameter PhaseDate is the "Date Assigned" range on table "Oversight". They are working correctly.
Of course, the conditional formatting needs to evaluate to TRUE or FALSE, so the equation in the conditional formatting editor also sets up TRUE if <7. To do this, I highlighted the Project IDs and in $A31 I navigated Conditional Formatting -> New Rule -> Use a formula to determine which cells to format
where I inputted the formula =AuditFormat($A31,Oversight[Date Assigned])<7
I tried cutting and pasting the formula or hand writing the formula. Excel consistently rejects my entry as a formula, however, the conditional formatting works fine to *reference* the results in either column B or column C to apply formatting to column A.We can't use structured reference and arrays in conditional formatting. Try =$C31 instead or use direct references.
