formatting cells base on value of other cells

[sanlen]

Hi,

I am trying to use conditional formatting to formate the cell base on the values of other cells.

I have attached the link to show the sample of the formatting that i am trying to show on my sheet.

http://www.box.net/shared/e08o6su5i3


Your comments will be very much appreciated and sorry for any inconvenience.

Thanks,
sanlen

 

[GlennUK]

I cannot view that link due to company access restrictions, and suppose that would be true of quite a few people here. Can you describe what you are aiming for?

 

[JamesW]

Hi,

No access to view your sheet at work, but maybe this would help:

Format -> Conditional Formatting -> Formula is: =IF($B$1="value",1,0)

 

[sanlen]

Hi,

I need to show the progress of the task in the map formatting. We have many tasks need to be completed on few different regions. We have 4 different milestones (MS01, MS02, MS03, MS04) to control the progress of the task too. What i am looking for is something like this:

- if MS01 is date then the color of cell sheet1!D5 is YELLOW (taks already started).
- if MS04 is date then the task already completed and color of cell sheet!D5 is BLUE
- if any of MS01 through MS04 contail work "ISSUE" then color of sheet1!D5 is RED.

Those will also be applied to the other cell.

Thanks,
sanlen

 

[JamesW]

Where are MS01 - MS04 stored?

 

[sanlen]

MS01 is at sheet1!J5
MS02 is at sheet1!K5
MS03 is at sheet1!L5
MS04 is at sheet1!M5

Thanks,
sanlen

 

[JamesW]

Hi sanlen,

Try:

Format -> Conditional Formatting -> Formula is:

Condition 1. =IF(OR($J$5:$M$5="Issue"),1,0) - Set to RED
Condition 2. =IF(CELL("format",M5)="D1",1,0) - Set to BLUE
condition 3. =IF(CELL("format",J5)="D1",1,0) - Set to YELLOW

 

[sanlen]

Hi,

Thanks.

But Condition 1. =IF(OR($J$5:$M$5="Issue"),1,0) does not work for me. I dont get any changes in my cell.

Please advice.

Thanks,
sanlen

 

[JamesW]

So in the range J5:M5 you have set some values to "Issue" and it isn't working? It works for me!

It may be that J5:M5 are headers (MS01 - MS04), and your values are actually in J6:M6, in which case change the formula accordingly.

 

[sanlen]

Hi,

It does not work in this way:

The task already started on 7/Mar/2011, so MS01=7/Mar/2011 and the activity of MS01 also already completed then i am ready to start MS02. Unfortunately, some problems occure during the process of MS02 so the value of this MS02 will be "ISSUE".

At this point my cell is still in YELLOW and not change from this YELLOW to RED.

MS01------MS02
7/Mar------ISSUE


Thanks,
sanlen