Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Scott Paine
 
Posts: n/a
Default cannot use ISEVEN or ISODD functions in Conditional Formatting

Attempting to use the ISEVEN() or ISODD() functions in Excel 2003 SP2 for
Windows or Excel 2004 for Mac OS X within a Conditional Formatting formula
produces the error:

"You may not use references to other worksheets or workbooks for Conditional
Formatting criteria."

Even though the formula works fine in a cell, the Conditional Formatting
glitch happens even with a new Excel file using a generic test formula such
as: =iseven(2)

These functions would be useful in Conditional Formatting to produce
alternating row formats (shading) for entire columns efficiently instead of
applying a defined style, which for entire columns will tend to greatly
degrade file performance.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default cannot use ISEVEN or ISODD functions in Conditional Formatting

This is because CF doesn't like ranges on another worksheet. Either give the
range a name, InsertNameDefine..., and use that, or use INDIRECT,
INDIRECT("Sheet2!A1:A20") for instance.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Scott Paine" <Scott wrote in message
...
Attempting to use the ISEVEN() or ISODD() functions in Excel 2003 SP2 for
Windows or Excel 2004 for Mac OS X within a Conditional Formatting formula
produces the error:

"You may not use references to other worksheets or workbooks for

Conditional
Formatting criteria."

Even though the formula works fine in a cell, the Conditional Formatting
glitch happens even with a new Excel file using a generic test formula

such
as: =iseven(2)

These functions would be useful in Conditional Formatting to produce
alternating row formats (shading) for entire columns efficiently instead

of
applying a defined style, which for entire columns will tend to greatly
degrade file performance.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow

this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.


http://www.microsoft.com/office/comm...et.f unctions



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Dibben
 
Posts: n/a
Default cannot use ISEVEN or ISODD functions in Conditional Formatting

ISEVEN and ISODD are funtions from the Analysis Toolpak, which is an add-in,
hence the message.

For alternating row shading based on CF, select a gaggle of rows then enter
this formula in CFFormula is.

=MOD(ROW(),2)=1

To get 3 and 3 as above.........

=MOD(ROUND(ROW()/3,0),2)=0

Alternate........=ROW()=ODD(ROW())


Gord Dibben Excel MVP

On Tue, 6 Dec 2005 12:42:02 -0800, "Scott Paine" <Scott
wrote:

Attempting to use the ISEVEN() or ISODD() functions in Excel 2003 SP2 for
Windows or Excel 2004 for Mac OS X within a Conditional Formatting formula
produces the error:

"You may not use references to other worksheets or workbooks for Conditional
Formatting criteria."

Even though the formula works fine in a cell, the Conditional Formatting
glitch happens even with a new Excel file using a generic test formula such
as: =iseven(2)

These functions would be useful in Conditional Formatting to produce
alternating row formats (shading) for entire columns efficiently instead of
applying a defined style, which for entire columns will tend to greatly
degrade file performance.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Scott Paine
 
Posts: n/a
Default cannot use ISEVEN or ISODD functions in Conditional Formatting

Oh yeah, I learned that from C++ years ago, before purging all my programming
knowledge to make room for recipes;-) Thanks.

"JE McGimpsey" wrote:...


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Scott Paine
 
Posts: n/a
Default cannot use ISEVEN or ISODD functions in Conditional Formatting

Your reply makes little sense to me b/c the problem happens with only one
sheet and no defined ranges. The functions must call other sheets or ranges
internally or something... The other solutions are simpler, but thanks
anyway:-)

"Bob Phillips" wrote:...
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Scott Paine
 
Posts: n/a
Default cannot use ISEVEN or ISODD functions in Conditional Formatting

Same as JE McGimpsey's reply, only thanks for pointing out I can use this
method for more than even/odd. Interesting possibilities... Thanks.

"Gord Dibben" wrote:...
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Nested functions in conditional formatting formulae Joseph Excel Discussion (Misc queries) 3 October 20th 05 11:52 PM
Conditional Formatting Error ddate Excel Worksheet Functions 0 May 5th 05 09:00 PM
difficulty with conditional formatting Deb Excel Discussion (Misc queries) 0 March 23rd 05 07:13 PM
Multiple FIND functions in Conditional Formatting RocketFuMaster Excel Worksheet Functions 2 March 2nd 05 07:09 PM
Conditional formatting not available in Excel BAB Excel Discussion (Misc queries) 2 January 1st 05 04:33 PM


All times are GMT +1. The time now is 08:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"