Conditional Formatting - valid formulas?

[dleseward]

I am trying to set conditional formats based on a table lookup rather than referencing another cell in the same sheet. does anyone know if this is possible?

Example:

Table (defined as an Excel Table on Sheet 2):
Headers> "Column A", "Column B", "Column C"
Row1> 001, "OK", £100
Row2> 002, "NOTOK",£200,
etc

Sheet 1:
A1 = 001 (a valid entry from Column A in the Table)

I want to set a conditional formatting rule in A1 based on a corresponding value in the Table, so I assumed a condition such as the following might work:

=index(Table,match($A1,Table[ColumnA],0),match("Column B",Table[#Headers],0))="OK"

However Excel 2010 reports an error when I try to enter this condition. Is INDEX not a allowable formula in conditional formats? and if not, is there a list of valid functions one can use?

Note, I know that this works if I create a formula in B1 that performs the lookup with Index and then set a conditional format for A1 based on the formula =$B1="OK". So that is not a problem. I just wanted to avoid having to do this.

thanks.

 

[jasonb75]

From excel help

Note When you create a conditional format, you can only reference other cells on the same worksheet; you cannot reference cells on other worksheets in the same workbook, or use external references to another workbook.

 

[dleseward]

OK, thanks. So this would have been OK if the Table had been on the same Sheet?

Note that if you go to Excel Help and lookup Conditional Formatting, it says the following:
"Conditional formatting across worksheets

You can use conditional formatting on cells that are referenced in another worksheet in the same workbook. This capability is not available between workbooks.
"



cheers,
David

 

[RoryA]

In Excel 2010 you can refer to cells on different worksheets directly in CF formulas. You cannot use table references though, as far as I can tell.

 

[dleseward]

OK thanks.

Makes sense as Chart formulas have the same problem. It seems the development team didn't enable Table references anywhere except in straight cell & array formulas.

cheers,
David

 

[T. Valko]

From excel help

Note When you create a conditional format, you can only reference other cells on the same worksheet; you cannot reference cells on other worksheets in the same workbook, or use external references to another workbook.

However Excel 2010 reports an error

Although I don't have Excel 2010, I've read that you can refer to other sheets for CF in Excel 2010.

Also, if I remember correctly, using formulas with the structured table syntax won't work in (Excel 2007) in refedits.

To the OP, try using regular A1 references in your formula.

 

[RoryA]

Also, as before, you can work around it by defining a name using the table-ref formula and then using that name in the CF dialog.