Conditional formatting based on values in table gives "name range" error

Question

Conditional formatting based on values in table gives "name range" error

I am trying to get Excel to apply conditional formatting to a table based on this formula:

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

Essentially, if a word Prawn

is in the Name1 column and a cell with the same row in the Name4 column is empty, Excel should apply highlighting.

The formula evaluates to True or False, usually when testing in a cell, but when I enter it into Conditional Formatting Excel throws an error in the name ranges. I've read that using the operator AND

in conditional formatting can cause problems, but not sure how to fix it in this situation.

+3

excel and-operator range excel-formula conditional-formatting

gherka 10 jan. '15 at 9:20 am

source to share

1 answer

pnuts · Accepted Answer · 2015-01-10T11:08:35+0000

Select your table (assuming it is table 1) and try:

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

The problem is not with the use of the operator AND

, but with the use of tables.

Conditional formatting based on values ​​in table gives "name range" error

More articles:

Conditional formatting based on values in table gives "name range" error