Conditional Formatting based on values in a Table gives 'name range' error

Question

I'm trying to make Excel apply Conditional Formatting to a Table based on this formula:

=AND([Name1]="Prawn", ISBLANK([Name4]))  

Essentially, if the word Prawn is in column Name1 and the same-row cell in column Name4 is blank, Excel should apply highlighting.

The formula evaluates to a True or False state normally when tested in a cell, but when I input it in Conditional Formatting, Excel throws up an error about name ranges. I read that using the AND operator in conditional formatting might cause issues, but not sure how to fix it in this situation.

Answer 1

Please select your table (assumed to be Table1) and try:

=AND(INDIRECT("Table1[@Name1]")="Prawn",ISBLANK(INDIRECT("Table1[@Name4]")))  

The issue is not with the use of the AND operator but with the use of Tables.