Conditional Formatting using OR and INDIRECT

[MartinBecks]

I want to use the following conditional format to highlight the row when anyone of these three is chosen from a drop down menu but nothing happens. I can however break it into 3 different conditional formats which works great but I want to apply this rule to several sheets thereby cutting it down to one formula.

=INDIRECT("C"&ROW())=OR(NEW, OLD, REUSED)

Please help. Make this conditional format work.

 

[skywriter]

Try this it worked fine when I tested it:
=OR(INDIRECT("C"&ROW())={"NEW","OLD","REUSED"})

Excel 2010
	A	B	C
1	TRUE		new
Sheet40

Cell Formulas
Range	Formula
A1	=OR(INDIRECT("C"&ROW())={"NEW","OLD","REUSED"})

 

[MartinBecks]

When I try using that as a formatting rule. I get the following error. "You may not use reference operations (such as unions, intersections, and ranges) or array constants for Conditional Formatting criteria."

Then when I tried changing the { to ( it says my formula has an error. And when I tried removing the { it accepted the formula but the conditional formatting feature did not work.

 

[skywriter]

The only thing that I could get to work is a helper cell where the formula can go. Then just point the conditional formatting to that cell.

Edit: Cells A1:C1 are highlighted green on my test spreadsheet, but for some reason are not green here. But this works just fine. If you need any help with entering the conditional formatting it's very easy. I did not enter three formulas into conditional formatting, only one, it just shows three because Excel copies it internally because of the way I entered it.

Sheet40

*	A	B	C
*	New

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]TRUE[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula A1 =OR(INDIRECT("C"&ROW())={"NEW","OLD","REUSED"}) <tbody> </tbody>

<tbody>
</tbody>

Conditional formatting

Cell Nr.: / Condition Format A1 1. / Formula is =$A$1=TRUE <tbody> [TD="bgcolor: #00b050"]Abc

<tbody>

B1	1. / Formula is =$A$1=TRUE
C1	1. / Formula is =$A$1=TRUE

[TD="bgcolor: #00b050"]Abc[/TD]

[TD="bgcolor: #00b050"]Abc[/TD]

</tbody>

[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4