Search code examples
excelexcel-formularangeconditional-formattingand-operator

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

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.

Solution

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