How to write a Conditional Formatting Formula inside an Excel Table

[GTS]

Using Excel 365.

Currently stumped on this one. I don't do a lot with Excel Tables, but am reasonably familiar with Conditional Formatting.
I have a Table (named _2016-2020).
I'm trying to build in some error checking (warnings) between columns. Eg - If the Status column indicates Sold, or the Job No column has an entry, then the Inquiry column should say Job. If it doesn't, highlight it.

I've created a new (helper) column (titled Column1 for right now) that has the following formula:
=IF(AND((OR([@STATUS]="0 - SOLD",[@[JOB_NO]]<>"")),[@[INQ/Q/JOB]]<>"JOB"),1,0)
This works fine, returning 1 or 0 appropriately.

I've tried to put this formula directly into Conditional Formatting, but I get the error message "The syntax of this name isn't correct".
I've tried to add in the Table Name, but no go. Not sure if it is a syntax issue or not.
I've tried to put in a simple formula that just looks at Column1 for the 1 or 0 (T or F), but can't get it to work either. Seems it either works on one row or all rows, not row by row as I want.

Does Conditional Formatting not understand Table Structured references?
Or do I have the syntax wrong?
Something else that I'm missing?

I have several checks like this that I would like to do. There must be an approach that works. Thanks.

 

[SanjayGMusafir]

Two things -

1. Conditional Format doesn't take Structured table references. rather use Cell address(es)
2. No need to use IF statement - Use the part that results in "Yes" or "1" - That is your formula for Conditional Format
   1. Conditional Format will perform if answer is "Yes" or "1"
   2. AND((OR([@STATUS]="0 - SOLD",[@[JOB_NO]]<>"")),[@[INQ/Q/JOB]]<>"JOB")
   3. But change the references to cell address(es)

Hope it helps in some useful way

 

[GTS]

Sanjay,

Thanks, that has resolved things. Using the cell references works.
I see that Conditional Formatting shows the same formula all the way down the column in the Table. All rows reference row 9 (the first row in my table).
Once I changed the "Applies to" to be the full range of the Table, then all worked as it should.
And a little testing indicates that adding rows to the table (at top or bottom or in between) is automatically captured (which is necessary).

Good point about not needing to wrap the AND and OR with the IF statement. Never would have thought of it that way. Makes sense though.

Appreciate the help.

GTS

 

[SanjayGMusafir]

Glad to help you and thanks for the feedback.