Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 67
Default Conditional format won't copy

Greetings all:

I am running XP Pro/Excel 2003.

I have a spreadsheet that I created to track my copy count for a copier on a
maintenance agreement so that I know how I am doing and to avoid those
expensive over contracted amount pay per page costs (cool, I know).

In any event, I have a colum that has a conditional format which will shade
the cell red if I am over my current allotment, and shade the cell green if I
am under. Simple enough, however I don't want any shading if the cell is
blank.

I have some fixed data in these cells:

B3 is my contracted copy amount
B7 is my starting copy count
A7 is my starting agreement date

I Have the following columns

A Current Date
B Current Machine Count
C Count to date formula =IF(ISBLANK(B8),"",(B8-$B$7))
D Day of Contract formula=IF(ISBLANK(B8),"",(A8-$A$7))
E Scheduled Count formula =IF(ISBLANK(B8),"",(($B$3/365)*D8))
F Over/Under Column formula=IF(ISBLANK(B8),"",(E8-C8))

It is this column F where I have the conditional format to shade it red or
green depending on if my current count is over or under my scheduled count.
The way it currently is working is that no number will print, but it will
shade the cell green. I assume it might have something to do with the fact
that I am using ISBLANK in the formula, but I really don't want to have to
copy and drag the conditonal format down each time I update the next line of
the worksheet with new data.

The conditonal format I am using is:

If f8 is less than 0 it will shade the cell red
If f8 is greater than 0 it will shade the cell green

I have tried using a third condition of:

If f8 is 0 then no shading, but this will not work. I always get a green
fill.

Anyone have any ideas?

Sorry for the lenght, but I feel more info is better than less!

Mark
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 740
Default Conditional format won't copy

Try to rearrange this the conditonal format you are using:

1st f8 is equal to 0 it will shade "no color"
2nd f8 is less than 0 it will shade the cell "red"
3rd f8 is greater than 0 it will shade the cell "green"

I have tried using a third condition of:

it works with a logical arrangement.

"MarkT" wrote:

Greetings all:

I am running XP Pro/Excel 2003.

I have a spreadsheet that I created to track my copy count for a copier on a
maintenance agreement so that I know how I am doing and to avoid those
expensive over contracted amount pay per page costs (cool, I know).

In any event, I have a colum that has a conditional format which will shade
the cell red if I am over my current allotment, and shade the cell green if I
am under. Simple enough, however I don't want any shading if the cell is
blank.

I have some fixed data in these cells:

B3 is my contracted copy amount
B7 is my starting copy count
A7 is my starting agreement date

I Have the following columns

A Current Date
B Current Machine Count
C Count to date formula =IF(ISBLANK(B8),"",(B8-$B$7))
D Day of Contract formula=IF(ISBLANK(B8),"",(A8-$A$7))
E Scheduled Count formula =IF(ISBLANK(B8),"",(($B$3/365)*D8))
F Over/Under Column formula=IF(ISBLANK(B8),"",(E8-C8))

It is this column F where I have the conditional format to shade it red or
green depending on if my current count is over or under my scheduled count.
The way it currently is working is that no number will print, but it will
shade the cell green. I assume it might have something to do with the fact
that I am using ISBLANK in the formula, but I really don't want to have to
copy and drag the conditonal format down each time I update the next line of
the worksheet with new data.

The conditonal format I am using is:

If f8 is less than 0 it will shade the cell red
If f8 is greater than 0 it will shade the cell green

I have tried using a third condition of:

If f8 is 0 then no shading, but this will not work. I always get a green
fill.

Anyone have any ideas?

Sorry for the lenght, but I feel more info is better than less!

Mark

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 67
Default Conditional format won't copy

I have tried that suggestion but I still have the same problem. I have had
all three different arguements in various order with the same results.

Thanks,

"driller" wrote:

Try to rearrange this the conditonal format you are using:

1st f8 is equal to 0 it will shade "no color"
2nd f8 is less than 0 it will shade the cell "red"
3rd f8 is greater than 0 it will shade the cell "green"

I have tried using a third condition of:

it works with a logical arrangement.

"MarkT" wrote:

Greetings all:

I am running XP Pro/Excel 2003.

I have a spreadsheet that I created to track my copy count for a copier on a
maintenance agreement so that I know how I am doing and to avoid those
expensive over contracted amount pay per page costs (cool, I know).

In any event, I have a colum that has a conditional format which will shade
the cell red if I am over my current allotment, and shade the cell green if I
am under. Simple enough, however I don't want any shading if the cell is
blank.

I have some fixed data in these cells:

B3 is my contracted copy amount
B7 is my starting copy count
A7 is my starting agreement date

I Have the following columns

A Current Date
B Current Machine Count
C Count to date formula =IF(ISBLANK(B8),"",(B8-$B$7))
D Day of Contract formula=IF(ISBLANK(B8),"",(A8-$A$7))
E Scheduled Count formula =IF(ISBLANK(B8),"",(($B$3/365)*D8))
F Over/Under Column formula=IF(ISBLANK(B8),"",(E8-C8))

It is this column F where I have the conditional format to shade it red or
green depending on if my current count is over or under my scheduled count.
The way it currently is working is that no number will print, but it will
shade the cell green. I assume it might have something to do with the fact
that I am using ISBLANK in the formula, but I really don't want to have to
copy and drag the conditonal format down each time I update the next line of
the worksheet with new data.

The conditonal format I am using is:

If f8 is less than 0 it will shade the cell red
If f8 is greater than 0 it will shade the cell green

I have tried using a third condition of:

If f8 is 0 then no shading, but this will not work. I always get a green
fill.

Anyone have any ideas?

Sorry for the lenght, but I feel more info is better than less!

Mark

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 740
Default Conditional format won't copy

got your clear point...some mystery for that but let s not quit...

try this...on your cell F8...cond formatting
1st formula is = f8=0 to shade "no color"
2nd cell value is less than 0 it will shade the cell "red"
3rd cell value is greater than 0 it will shade the cell "green"


"MarkT" wrote:

I have tried that suggestion but I still have the same problem. I have had
all three different arguements in various order with the same results.

Thanks,

"driller" wrote:

Try to rearrange this the conditonal format you are using:

1st f8 is equal to 0 it will shade "no color"
2nd f8 is less than 0 it will shade the cell "red"
3rd f8 is greater than 0 it will shade the cell "green"

I have tried using a third condition of:

it works with a logical arrangement.

"MarkT" wrote:

Greetings all:

I am running XP Pro/Excel 2003.

I have a spreadsheet that I created to track my copy count for a copier on a
maintenance agreement so that I know how I am doing and to avoid those
expensive over contracted amount pay per page costs (cool, I know).

In any event, I have a colum that has a conditional format which will shade
the cell red if I am over my current allotment, and shade the cell green if I
am under. Simple enough, however I don't want any shading if the cell is
blank.

I have some fixed data in these cells:

B3 is my contracted copy amount
B7 is my starting copy count
A7 is my starting agreement date

I Have the following columns

A Current Date
B Current Machine Count
C Count to date formula =IF(ISBLANK(B8),"",(B8-$B$7))
D Day of Contract formula=IF(ISBLANK(B8),"",(A8-$A$7))
E Scheduled Count formula =IF(ISBLANK(B8),"",(($B$3/365)*D8))
F Over/Under Column formula=IF(ISBLANK(B8),"",(E8-C8))

It is this column F where I have the conditional format to shade it red or
green depending on if my current count is over or under my scheduled count.
The way it currently is working is that no number will print, but it will
shade the cell green. I assume it might have something to do with the fact
that I am using ISBLANK in the formula, but I really don't want to have to
copy and drag the conditonal format down each time I update the next line of
the worksheet with new data.

The conditonal format I am using is:

If f8 is less than 0 it will shade the cell red
If f8 is greater than 0 it will shade the cell green

I have tried using a third condition of:

If f8 is 0 then no shading, but this will not work. I always get a green
fill.

Anyone have any ideas?

Sorry for the lenght, but I feel more info is better than less!

Mark

  #5   Report Post  
Member
 
Location: London
Posts: 78
Default

It's not the order of conditions that's wrong - it's just a simple tweak to the formulae that are required.

You have in column F:
=IF(ISBLANK(B8),"",(E8-C8))

So E8-C8 will only be zero for a date that also has a time of 00:00:00 - and I imagine your dates probably have times in them? Or the values, being divided, are leaving remainders?

If so, anything that 'looks' like zero will probably be some real number between 0 and 1.

Therefore I would suggest you EITHER change the column F formula to:
=IF(ISBLANK(B8),"",INT(E8-C8))

or

Change your conditions to:
If f8 is less than 0 it will shade the cell red
If f8 is greater than equal to 1 it will shade the cell green

Just one thing though - doesn't a value of "" in Column F give you a red shading? I would expect it to, as "" is less than 0. Therefore to be sure I would also change the first condition to:
If =AND(f8 is less than 0,f8 less than greater than "") it will shade the cell red


BizMark


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 67
Default Conditional format won't copy

Nope, sorry, same darn thing.

I think it might have something to do with the 'Isblank', I'm not sure....

"driller" wrote:

got your clear point...some mystery for that but let s not quit...

try this...on your cell F8...cond formatting
1st formula is = f8=0 to shade "no color"
2nd cell value is less than 0 it will shade the cell "red"
3rd cell value is greater than 0 it will shade the cell "green"


"MarkT" wrote:

I have tried that suggestion but I still have the same problem. I have had
all three different arguements in various order with the same results.

Thanks,

"driller" wrote:

Try to rearrange this the conditonal format you are using:

1st f8 is equal to 0 it will shade "no color"
2nd f8 is less than 0 it will shade the cell "red"
3rd f8 is greater than 0 it will shade the cell "green"

I have tried using a third condition of:

it works with a logical arrangement.

"MarkT" wrote:

Greetings all:

I am running XP Pro/Excel 2003.

I have a spreadsheet that I created to track my copy count for a copier on a
maintenance agreement so that I know how I am doing and to avoid those
expensive over contracted amount pay per page costs (cool, I know).

In any event, I have a colum that has a conditional format which will shade
the cell red if I am over my current allotment, and shade the cell green if I
am under. Simple enough, however I don't want any shading if the cell is
blank.

I have some fixed data in these cells:

B3 is my contracted copy amount
B7 is my starting copy count
A7 is my starting agreement date

I Have the following columns

A Current Date
B Current Machine Count
C Count to date formula =IF(ISBLANK(B8),"",(B8-$B$7))
D Day of Contract formula=IF(ISBLANK(B8),"",(A8-$A$7))
E Scheduled Count formula =IF(ISBLANK(B8),"",(($B$3/365)*D8))
F Over/Under Column formula=IF(ISBLANK(B8),"",(E8-C8))

It is this column F where I have the conditional format to shade it red or
green depending on if my current count is over or under my scheduled count.
The way it currently is working is that no number will print, but it will
shade the cell green. I assume it might have something to do with the fact
that I am using ISBLANK in the formula, but I really don't want to have to
copy and drag the conditonal format down each time I update the next line of
the worksheet with new data.

The conditonal format I am using is:

If f8 is less than 0 it will shade the cell red
If f8 is greater than 0 it will shade the cell green

I have tried using a third condition of:

If f8 is 0 then no shading, but this will not work. I always get a green
fill.

Anyone have any ideas?

Sorry for the lenght, but I feel more info is better than less!

Mark

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 740
Default Conditional format won't copy

Im suppose to say the same-sorry-coz my excel 2003 here works fine...with
such conditional formatting...maybe u need to detect-repair your excel
there...good luck

"MarkT" wrote:

Nope, sorry, same darn thing.

I think it might have something to do with the 'Isblank', I'm not sure....

"driller" wrote:

got your clear point...some mystery for that but let s not quit...

try this...on your cell F8...cond formatting
1st formula is = f8=0 to shade "no color"
2nd cell value is less than 0 it will shade the cell "red"
3rd cell value is greater than 0 it will shade the cell "green"


"MarkT" wrote:

I have tried that suggestion but I still have the same problem. I have had
all three different arguements in various order with the same results.

Thanks,

"driller" wrote:

Try to rearrange this the conditonal format you are using:

1st f8 is equal to 0 it will shade "no color"
2nd f8 is less than 0 it will shade the cell "red"
3rd f8 is greater than 0 it will shade the cell "green"

I have tried using a third condition of:

it works with a logical arrangement.

"MarkT" wrote:

Greetings all:

I am running XP Pro/Excel 2003.

I have a spreadsheet that I created to track my copy count for a copier on a
maintenance agreement so that I know how I am doing and to avoid those
expensive over contracted amount pay per page costs (cool, I know).

In any event, I have a colum that has a conditional format which will shade
the cell red if I am over my current allotment, and shade the cell green if I
am under. Simple enough, however I don't want any shading if the cell is
blank.

I have some fixed data in these cells:

B3 is my contracted copy amount
B7 is my starting copy count
A7 is my starting agreement date

I Have the following columns

A Current Date
B Current Machine Count
C Count to date formula =IF(ISBLANK(B8),"",(B8-$B$7))
D Day of Contract formula=IF(ISBLANK(B8),"",(A8-$A$7))
E Scheduled Count formula =IF(ISBLANK(B8),"",(($B$3/365)*D8))
F Over/Under Column formula=IF(ISBLANK(B8),"",(E8-C8))

It is this column F where I have the conditional format to shade it red or
green depending on if my current count is over or under my scheduled count.
The way it currently is working is that no number will print, but it will
shade the cell green. I assume it might have something to do with the fact
that I am using ISBLANK in the formula, but I really don't want to have to
copy and drag the conditonal format down each time I update the next line of
the worksheet with new data.

The conditonal format I am using is:

If f8 is less than 0 it will shade the cell red
If f8 is greater than 0 it will shade the cell green

I have tried using a third condition of:

If f8 is 0 then no shading, but this will not work. I always get a green
fill.

Anyone have any ideas?

Sorry for the lenght, but I feel more info is better than less!

Mark

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,886
Default Conditional format won't copy

Hi Mark

Maybe because of the way that binary representations of numbers are
held, your Zero values are not truly zero but
0.0000000000565 or some such value.
As such, they will shade green according to the test.
If you made the test for green greater than 1, then any value from say
0.0000000001 through 0.9999999999
will remain uncoloured.

--
Regards

Roger Govier


"MarkT" wrote in message
...
Greetings all:

I am running XP Pro/Excel 2003.

I have a spreadsheet that I created to track my copy count for a
copier on a
maintenance agreement so that I know how I am doing and to avoid those
expensive over contracted amount pay per page costs (cool, I know).

In any event, I have a colum that has a conditional format which will
shade
the cell red if I am over my current allotment, and shade the cell
green if I
am under. Simple enough, however I don't want any shading if the cell
is
blank.

I have some fixed data in these cells:

B3 is my contracted copy amount
B7 is my starting copy count
A7 is my starting agreement date

I Have the following columns

A Current Date
B Current Machine Count
C Count to date formula =IF(ISBLANK(B8),"",(B8-$B$7))
D Day of Contract formula=IF(ISBLANK(B8),"",(A8-$A$7))
E Scheduled Count formula =IF(ISBLANK(B8),"",(($B$3/365)*D8))
F Over/Under Column formula=IF(ISBLANK(B8),"",(E8-C8))

It is this column F where I have the conditional format to shade it
red or
green depending on if my current count is over or under my scheduled
count.
The way it currently is working is that no number will print, but it
will
shade the cell green. I assume it might have something to do with the
fact
that I am using ISBLANK in the formula, but I really don't want to
have to
copy and drag the conditonal format down each time I update the next
line of
the worksheet with new data.

The conditonal format I am using is:

If f8 is less than 0 it will shade the cell red
If f8 is greater than 0 it will shade the cell green

I have tried using a third condition of:

If f8 is 0 then no shading, but this will not work. I always get a
green
fill.

Anyone have any ideas?

Sorry for the lenght, but I feel more info is better than less!

Mark



  #9   Report Post  
Posted to microsoft.public.excel.misc
p p is offline
external usenet poster
 
Posts: 12
Default Conditional format won't copy

zzzzzzzzzzzzz?

"Roger Govier" wrote:

Hi Mark

Maybe because of the way that binary representations of numbers are
held, your Zero values are not truly zero but
0.0000000000565 or some such value.
As such, they will shade green according to the test.
If you made the test for green greater than 1, then any value from say
0.0000000001 through 0.9999999999
will remain uncoloured.

--
Regards

Roger Govier


"MarkT" wrote in message
...
Greetings all:

I am running XP Pro/Excel 2003.

I have a spreadsheet that I created to track my copy count for a
copier on a
maintenance agreement so that I know how I am doing and to avoid those
expensive over contracted amount pay per page costs (cool, I know).

In any event, I have a colum that has a conditional format which will
shade
the cell red if I am over my current allotment, and shade the cell
green if I
am under. Simple enough, however I don't want any shading if the cell
is
blank.

I have some fixed data in these cells:

B3 is my contracted copy amount
B7 is my starting copy count
A7 is my starting agreement date

I Have the following columns

A Current Date
B Current Machine Count
C Count to date formula =IF(ISBLANK(B8),"",(B8-$B$7))
D Day of Contract formula=IF(ISBLANK(B8),"",(A8-$A$7))
E Scheduled Count formula =IF(ISBLANK(B8),"",(($B$3/365)*D8))
F Over/Under Column formula=IF(ISBLANK(B8),"",(E8-C8))

It is this column F where I have the conditional format to shade it
red or
green depending on if my current count is over or under my scheduled
count.
The way it currently is working is that no number will print, but it
will
shade the cell green. I assume it might have something to do with the
fact
that I am using ISBLANK in the formula, but I really don't want to
have to
copy and drag the conditonal format down each time I update the next
line of
the worksheet with new data.

The conditonal format I am using is:

If f8 is less than 0 it will shade the cell red
If f8 is greater than 0 it will shade the cell green

I have tried using a third condition of:

If f8 is 0 then no shading, but this will not work. I always get a
green
fill.

Anyone have any ideas?

Sorry for the lenght, but I feel more info is better than less!

Mark




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 Formating. Remove condition, keep format? Mr. Wing Excel Discussion (Misc queries) 0 September 14th 06 07:15 PM
Copy Formula Is conditional format Paul B. Excel Worksheet Functions 4 August 29th 06 10:55 PM
Conditional Format Not Working KMH Excel Discussion (Misc queries) 0 December 22nd 05 06:32 PM
How do I copy data in single cell format to a merged cell format Paul Excel Discussion (Misc queries) 1 June 27th 05 11:00 AM
Office2000: Conditional format behaves strangely Arvi Laanemets Excel Discussion (Misc queries) 1 April 7th 05 08:47 AM


All times are GMT +1. The time now is 11:19 AM.

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"