status of the project

Hi,

I would like to show the status of the project as below:

- If any of milestones MS01 to MS04 is containing date then i would like show the word "YELLOW" in my sheet2

- if any of the milestones MS01 to MS04 is containing PROBLEM then i would like to show the word "RED" in my sheet2.

- if milestone MS05 containing date then i would like to show the word "BLUE" in my sheet2

- if milestones MS01 to MS05 is blank then i would like to show the word "WHITE" in my sheet

I attached the worksheet along.

Please kindly advice. Your comments will ber very much appreciated.

which of the first two takes precedence?

actually, what is the order of precedence for all conditions?

Make sure that Sheet2 column A starts at Row 2 to match up with Sheet 1 rows...

then select B2:B10 in Sheet2 and invoke Conditional formatting from the Home tab, select Manage Rules.

Select Use a formula to determine which cells to format and then enter formula:

=ISNUMBER(INDIRECT("'Sheet1'!F"&ROW()))

click Format and choose Blue from Pattern tab.

Click Ok, click New Rule and repeat above with formula:

=ISNUMBER(MATCH("Problem",INDIRECT("'Sheet1'!B"&ROW()&":E"&ROW()),0))

click Format and choose Red from Pattern tab.

Click Ok, click New Rule and repeat above with formula:

=COUNT(INDIRECT("'Sheet1'!B"&ROW()&":E"&ROW()))

click Format and choose Yellow from Pattern tab.

Click Ok.

Make sure that the order shown is Yellow, Red, Blue.

The White would be the default.

Click Ok and check that it is working as expected.

Sorry, that was my misunderstanding... try formula:

Please Login or Register  to view this content.

copied down

Can you fill in all possible combinations in your sample and then complete the Sheet2 manually so that we can see exactly the scheme you are looking at (and indicating priorities, if any, if more than one condition could be applied).

Also, are the ID lined up by rows 1:1 or can the ID's be listed in different order or some may be missing, therefore skewing the sequencing?

BTW, it is always best to give detail at the start, instead of wasting time and adding completely different condtions as you go.

Does this work in C2, copied down?

Please Login or Register  to view this content.

The 5 is compared to the result of:

SUMPRODUCT(ISNUMBER(SEARCH(A2,Sheet1!$B$3:$B$11))*(Sheet1!$C$2:$L$2="AC")*ISNUMBER(Sheet1!$C$3:$L$11))

and yes it is referring to the number of columns... so that if all the columns have a date, then it is TRUE.