Conditional Formatting formula not acceptable?

[Thief_]

I'm trying to Conditional Format a number of cells using "Formula Is" &
"=ISODD(INT($f2))=True", ie. if the value in F2 is odd, format the data. I
keep getting the message "You may not use references to other worksheets or
workbooks for Conditional Formatting Criteria". Why am I getting this error?

I used this formula instead:

=MOD($F358,2)<>0

But was interested to know why my original wasn't acceptable.

XL2003
WinXPSP2

 

[Dave Peterson]

=isodd() is in the analysis toolpak (tools|addins).

It's not built into excel.

 

[Thief_]

Yeah?! I know that, and ISODD does work, but for some reason the whole
formula doesn't work in CF.

 

[Biff]

Hi!

Personally, I would use MOD but you can use functions from ATP if you use
them in association with a defined name.

Insert>Name>Define
Name: Odd
Refers to: =ISODD(Sheet1!$A$1)

Then

Conditional Formatting
Formula is: =Odd

The only problem with this is that in the above situation using Named
formulas or ranges, you have to use absolute referencing so the above cf
formula will only work in a specific cell.....but there is a work around for
that....

You can use R1C1 referencing:

Insert>Name>Define
Name: Odd
Refers to: =ISODD(INDIRECT("RC",FALSE))

Now, when this used for the conditional formatting the named formula
references the cell that the cf is being applied to.

Biff

 

[Dave Peterson]

The formula may work in another cell, but that message you get about references
to other worksheets means that =isodd() won't work in the Conditional Formatting
formula.