Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 7th 05, 08:23 AM
Arvi Laanemets
 
Posts: n/a
Default Office2000: Conditional format behaves strangely

Hi

On a worksheet with premade formulas I used conditional format to mark out
cells with formulas for rows where key kolumn cell (week number - the sring
in format "yyyy.ww") is not empty. The condition p.e. for cell AA126 is set
to
Formula Is =($B1026<"")
and when TRUE, then the cell is filled pale blue with dashed borders
(default format is no fill, no borders, color automatic), when not, the cell
looks unformatted like any empty cell. It works excellent.

There was a need for additional conditional format check: the sum of 2
columns values must always equal with value in 3rd column - otherwise there
is some wrong entry on row (p.e. some value of wrong type was copied into
some cell). P.e. for cell AB126 is the conditional format now:
1)
Formula Is =(SUM(AC1026:AD1026)<AB1026)
when TRUE, the fill is pale blue, borders are dashed, and font is dark red;
2)
Formula Is =($B1026<"")
when TRUE, the fill is pale blue and borders are dashed

This format works, when there is a week number in cell B1026 - when
SUM(AC1026:AD1026)=AB1026, then cell is colored and font is black
(automatic), when the sum doesn't equal with test value, the font is red in
colored cell. But when the cell B1026 is empty, the cell is formatted like
the second condition returned TRUE - it's colored and has dashed border!

For testing I copied the formula from second condition into free cell on
worksheet - the formula returns FALSE. Adding 3rd condition:
Formula Is =($B1026="")
with formats no fill, no borders, automatic color
didn't help either. Has someone any ideas about this?

Thanks in advance!

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets




  #2   Report Post  
Old April 7th 05, 08:47 AM
Arvi Laanemets
 
Posts: n/a
Default

Hi


Forget it! The cause was 1st condition, which was false for cases when
formula in column AB returned "". I simply changed the first condition to:
Formula Is =(SUM(AC1026:AD1026)<SUM(AB1026))

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Arvi Laanemets" wrote in message
...
Hi

On a worksheet with premade formulas I used conditional format to mark out
cells with formulas for rows where key kolumn cell (week number - the

sring
in format "yyyy.ww") is not empty. The condition p.e. for cell AA126 is

set
to
Formula Is =($B1026<"")
and when TRUE, then the cell is filled pale blue with dashed borders
(default format is no fill, no borders, color automatic), when not, the

cell
looks unformatted like any empty cell. It works excellent.

There was a need for additional conditional format check: the sum of 2
columns values must always equal with value in 3rd column - otherwise

there
is some wrong entry on row (p.e. some value of wrong type was copied into
some cell). P.e. for cell AB126 is the conditional format now:
1)
Formula Is =(SUM(AC1026:AD1026)<AB1026)
when TRUE, the fill is pale blue, borders are dashed, and font is dark

red;
2)
Formula Is =($B1026<"")
when TRUE, the fill is pale blue and borders are dashed

This format works, when there is a week number in cell B1026 - when
SUM(AC1026:AD1026)=AB1026, then cell is colored and font is black
(automatic), when the sum doesn't equal with test value, the font is red

in
colored cell. But when the cell B1026 is empty, the cell is formatted like
the second condition returned TRUE - it's colored and has dashed border!

For testing I copied the formula from second condition into free cell on
worksheet - the formula returns FALSE. Adding 3rd condition:
Formula Is =($B1026="")
with formats no fill, no borders, automatic color
didn't help either. Has someone any ideas about this?

Thanks in advance!

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets







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
Conditional format problem PJ Excel Discussion (Misc queries) 2 March 11th 05 02:05 PM
conditional format Julian Campbell Excel Worksheet Functions 2 December 15th 04 11:59 PM
copy conditional format as ACTUAL format Dana Zulager Excel Discussion (Misc queries) 7 December 7th 04 11:02 PM
Copying a conditional format Meaux Excel Worksheet Functions 2 November 29th 04 10:19 AM
Conditional Format With SUMIF Minitman Excel Worksheet Functions 3 November 1st 04 02:58 PM


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

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017