Conditional Format Using Formula By Referring To Another Sheet

[paul_pearson]

Hi

I need help setting up Conditional Formatting please. For Sheet 2 Cell B2 how can It reference Sheet 1 cells C7:P7 and then through Conditional Format colour Sheet 2 cell B2 either White,Yellow,Red or Green

1. If the cells C7:P7 has a "G" it is worth 2 points , if the cells has a "S" the cell is worth 1 point & empty cell is worth 0 points...
2. If C7:P7 points total <4 points the Sheet 2 cell B2 colour is White , If C7:P7 points total between 5 and 8 points the Sheet 2 cell B2 colour is Yellow , If C7:P7 points total 9 and 12 points the Sheet 2 cell B2 colour is Red and If C7:P7 points total >13 points the Sheet 2 cell B2 colour is Green

How can I have a Conditional Format system for Sheet 2 Cell B2 which looks at Sheet 1 and totals the points from C7:P7 and then colours according to points

Thanks

Excel 2010

[Excel Fox]

Conditional format in Sheet2!B2=SUMPRODUCT(LOOKUP(rngColors,Vect,Rslt)), and equate it to >=13 for Green, >=9 for Red and >=5 for Yellow

Where,

rngColors=Sheet1!$C$7:$P$7
Vect={"G","S",0}
Rslt={2,1,0}

[paul_pearson]

Thanks Excel Fox.That is excellent

I am trying to learn how you created.

I see where you used a name range for the cells = rngColors
Also i see you put in 2 entries into the name manager.Does the CF formula look at the Vect={"G","S",0} & Rslt={2,1,0} from the name manager or are these somewhere else in the spreadsheet?

You used { } ..does this mean this is a Array formula , if not what is the value of using { } ?

I have a lot more of these to add.Would you please add on the extra 3 with formulas and i will study and add all the rest

Thanks for your help

Paul

[Excel Fox]

Yes, they are named ranges, and are added in the name manager. They are not there on the spreadsheet anywhere.

Yes, the curly brackets are used to pass the values as an array.

[Excel Fox]

Using 4 named ranges for the 4 categories.

rngTransfer, rngTrucking, rngDriving, rngForklift

Replaced the previous LOOKUP formula with B2=SUMPRODUCT(LOOKUP(INDIRECT("rng"&B2),Vect,Rslt))> =13

and copied that format to all the other 3 cells also

[paul_pearson]

Thank you

I added the 4 names and changed the formula but I have done something wrong here as it does not work

Thanks for any help

Paul

[Excel Fox]

Here's how you do it...

[paul_pearson]

Excellent.Thank you

What is the best way for me to copy those 4 cells to other cells in the sheet 2 with the same fuctionality.. say paste in F2 to F5

[Excel Fox]

You just need to copy the format, and paste it to the other cells really. Note that the range that you define should have the same naming convention as the ranges I've mentioned above. That's why the INDIRECT("rng"&B2) works as rngTransfer range.

[paul_pearson]

Thanks Excel Fox

Appreciated

If I want to add say another 20 different named cells all I do is follow how the above is set up.

How does this function work - INDIRECT("rng"&B2)

Thanks for all the help