0

I'm trying to conditionally format a column of cells based on whether the combination of two other columns appear in a Table.

Here is a link to the test workbook I am playing with and screenshots below for those that don't like clicking strangers links!

https://1drv.ms/x/s!Al1Kq21dFT1ij4ktFd0mzBniNX00tQ?e=L6aQm4

enter image description here

On the far left is an Excel table ([Table1]) that contains a list of valid combinations of [Category] and [Item]

  • Columns E&F contain some sample data to test against
  • Column G is the number of matching combinations I expect to return from a COUNTIFS() function
  • Column H is simply the same formula compared to 0 so I get a boolean result.
  • The actual formula to get the result shown in Column H is =COUNTIFS(Table1[Category],"="&E4, Table1[Item],"="&F4)=0

All the above works as expected.

In Column J is just some literal text with conditional formatting. The condition is simply =H4, again this works as expected.

Now to the problem... I want to avoid having the helper column (H) so I thought I could just use the same formula that I used in column H, as my condition formula.

So, I tried to use this in the conditional formatting formula dialog.

=COUNTIFS(Table1[Category],"="&E4, Table1[Item],"="&F4)=0

and with parantheses

=(COUNTIFS(Table1[Category],"="&E4, Table1[Item],"="&F4)=0)

enter image description here

Unfortunately, this results in the generic "There's a problem with this formula" error message.

If might be that there are some limitations with conditional formatting formulae that I'm not aware of (I'm no Excel guru, I'm a SQL developer really).

BTW: I need to stick with using a table as my real-world scenario is that there will be several tables, all populated from a database via a separate process with lengths varying from 2 or 3 entries to potentially thousands.

I would appreciate any help, even if it's just to say "You can't do this, you'll need to use your helper column..."

Thanks for looking...

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • 1
    You will need to use INDIRECT if you want to use structured references: `INDIRECT("Table1[Item]")` – Scott Craner Oct 05 '20 at 17:10
  • So your formula would be `=COUNTIFS(INDIRECT("Table1[Category]");"="&E4;INDIRECT("Table1[Item]");"="&F4)=0` – Wizhi Oct 05 '20 at 19:35
  • Thanks Both. Using `=COUNTIFS(INDIRECT("Table1[Category]"),"="&E4, INDIRECT("Table1[Item]"),"="&F4)=0` worked perfectly. @Scott Craner, if you want to add as an answer I will accept. @Wizhi, you comment was also useful but replacing ';' with ',' – Alan Schofield Oct 05 '20 at 19:52

0 Answers0