Evaluate/summarize range of text to populate summary based on specific logic defined

[ziggystardustz]

Hi all,

I need to evaluate values from a list on one worksheet and summarize the answers on to another worksheet using specific logic. I don't write code so I seem to be lost. I can't figure this out based on other examples given because it's not an exact match to mine. here goes...

Working with two worksheets, First worksheet is worksheet 1 and it has two columns, A for "Area" and B for "Status."

Second worksheet has 3 columns, A for Area, B for Task, C for Status. Example data:

Column A
Area
Project Team

Column B
Task
Documentation
UAT
Support
Training Materials

Column C
Status
G
G
Y
R


Values in "Status" of Column c of worksheet 2 are selected from a named list that contain the values G, Y, R and a blank. each has associated conditional formatting so G=Green, Y=Yellow, R=Red and Blank is no fill.

I need the status in column B of worksheet 1 to disaply automatically based on selections made in column C of worksheet 2 based on the following logic:

If one or more cells in the range (C2-C5) in column C worksheet 2 equals G or blank, with no R or Y, display G (with green background) in cell 2b on worksheet 1.

if at least one of the cells in range (C2-C5) in column C equals Y, with no R, display Y with yellow background) in cell 2b on worksheet 1. (overrides any green or blank)

if at least one of the cells in range (C2-C5) in column C equals R, display R with red background in cell 2b on worksheet 1. (ignores green, blank and yellow)

I'd also like to add this condition to the listing if possible:

if all cells in the range are G/green, display Completed with green background in cell 2b on worksheet 1.

I am not an advanced excel user so any help you can give would be greatly appreciated.

 

[bertie]

Ok! This is going to be one big and messy formula.
Using a basic if statement: If(condition is true, do this , else do this)

Taking your criteria back to front, the order determines which condition overrides the others.

Condition 1
If at least one of the cells in range (C2-C5) in column C equals R, display R with red background in cell 2b on worksheet 1. (ignores green, blank and yellow)

=IF(COUNTIF(Sheet2!$C$2:$C$5,"R")>0,"R")

Condition 2
If one or more cells in the range (C2-C5) in column C worksheet 2 equals G or blank, with no R or Y, display G (with green background) in cell 2b on worksheet 1.

=IF(AND(COUNTIF(Sheet2!$C$2:$C$5,"Y")>0,COUNTIF(Sheet2!$C$2:$C$5,"R")=0),"Y")

And this become the "Else" of the formula for condition 1

Condition 3
If one or more cells in the range (C2-C5) in column C worksheet 2 equals G or blank, with no R or Y, display G (with green background) in cell 2b on worksheet 1.

=IF(COUNTIF(Sheet2!$C$2:$C$5,"R")+COUNTIF(Sheet2!$C$2:$C$5,"Y")=0,"G")

Which becomes the "Else" part of the now combined Condition 1 and Condition 2 formula.

Now the combined condition 1,2 and 3 formula becomes the else part of:

Condition 4
if all cells in the range are G/green, display Completed with green background in cell 2b on worksheet 1.

=IF(COUNTIF(Sheet2!$C$2:$C$5,"G")=4,"Completed",all that stuff that went before)

Giving us the big messy formula:

=IF(COUNTIF(Sheet2!$C$2:$C$5,"G")=4,"Completed",IF(COUNTIF(Sheet2!$C$2:$C$5,"R")>0,"R",IF(AND(COUNTIF(Sheet2!$C$2:$C$5,"Y")>0,COUNTIF(Sheet2!$C$2:$C$5,"R")=0),"Y",IF(COUNTIF(Sheet2!$C$2:$C$5,"R")+COUNTIF(Sheet2!$C$2:$C$5,"Y")=0,"G"))))

Apply conditional formatting for the colour effects