Excel formula working in cell, but throws error in Conditional formatting

[szita2000]

Hi guys.

I came across something peculiar.

I made this formula for a conditional formula as I always did. (To the side of the table I create the "will be" conditional formula in a cell)
It was evaluating correctly in the worksheet cell.
Then when I went and copied in the formula for the conditional formula it throws an error.

It is weird because the formula evaluates in a cell as intended. Maybe the LET function is not liked by the conditional formula engine?

The formula:

=IF(OR($BU6="",DMSTable[@[04/07/2022]]=""),0,LET(GlidePath,INDEX($BY$6:$CJ$48,MATCH(DMSTable[@Measure],DMSTable[Measure],0),MATCH(H$2,$BY$2:$CJ$2,0)),
IFS($BU6="<",(DMSTable[@[04/07/2022]]<GlidePath),
$BU6="<=",(DMSTable[@[04/07/2022]]<=GlidePath),
$BU6=">",(DMSTable[@[04/07/2022]]>GlidePath),
$BU6=">=",(DMSTable[@[04/07/2022]]>=GlidePath),
$BU6="=",(DMSTable[@[04/07/2022]]=GlidePath))))

Quick explanation for what I am trying to achieve:
On the left there is a table with weeks, above it numbers for each week. 7 belongs to July, 8 to Aug and so on. Row2. - In this table we will have weekly team scores.
To the right there is a table with the glidepath (target) for each month. Table with month numbers above in row 2. - In this table we want to set the targets where a conditional formula will go green or red.

Between the two tables there are two columns one for make the result Red on the left hand side and one for Green. (BU:BV)
This columns holding the logical operators that the managers can set up. So the team results (left table) will color based on the glidepath table (right hand)

Something like this. The two helper column for the formula to work out then copy in the conditional formatting is BW6 and BX6.

Anyone have any idea what is wrong with my formula for the conditional formatting?
Thanks

 

[bebo021999]

The image was not clear enough to watch.
Most common error is the result from formula is not like "TRUE/FALSE".
Is the formula result "true" or "false"? If not, CF engine does not work.

 

[RoryA]

You can't use structured referencing in conditional formatting.

 

[szita2000]

You can't use structured referencing in conditional formatting.

Thanks Rory.

Is there any way that I can keep the table intact and still have the conditional formatting?
Ie. If I rewrite the formula and override the structural reference with ranges, it will work?
edit:grammar.

 

[RoryA]

Yes, you can just use regular range references.

 

[szita2000]

Yes, you can just use regular range references.

Thanks guys. It's working now.

I replaced the structured references with plain old cell and range references and it is working. It even uses the Named calculation from the LET function.