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