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

[paul_pearson]

Hi

I started adding extra cells in Sheet 2.I added Sparging and it works except for the Green colour in CF...The Yellow and Red CF works but Green does not....The formulas are all the same...what am i missing please?

Thanks
Paul

[Excel Fox]

It's all correct, except for one small mistake. The order of the conditional statements is critical. So if you first check whether the value is smaller than a smaller number, it will be true for the larger number. Similarly, if you first check whether the value is larger than a large number, it will be true for a smaller number also. So the equation that has the >13 in the formula, and which has the green color, should be in top.

[paul_pearson]

Thanks Excel Fox
Much appreciated

[paul_pearson]

Hi Excel Fox

This is such a good formula and will help me with other spreadsheets

Could you please explain the formula - =SUMPRODUCT(LOOKUP(INDIRECT("rng"&C2),Vect,Rslt))>=5

Also how does the formula know to look at the Name Manager as it is not on the Sheet anywhere the Vect & Rslt.They are only in the Name Manager

Thanks for all your help

Paul

[Excel Fox]

Note that the names in the name manager has a scope at Workbook level, and NOT at the sheet level. Any named defined as such will be accessible (or readable) from any worksheet. And to your question of how the formula looks at names manager, even though it is not on the sheet, well, I guess that's the beauty of a name object in Excel (MS designed it to work that way).

By the way, you can actually use =SUM(LOOKUP(INDIRECT("rng"&B2),Vect,Rslt))>=13 since it is being used in a conditional format, and formulas used within conditional formats will work as an array formula based on how it is written.

[paul_pearson]

Hi

I am not sure if the following is possible

1. Sheet 1 has the operators names with each category either a "G","S"," ". If I enter 1 of these names in Sheet 2 Cell B2 can it then look at the persons name from Sheet 1 and fill in each category with a colour depending on how many points for each category. Currently fills colour using CF for each category
2. Can this be done with formula or does this need to be done with VBA

Thanks

Paul

[Excel Fox]

Try this...

Used conditional format formula =SUMPRODUCT(LOOKUP(OFFSET(INDIRECT("rng"&B3),rngNameIndex,0),Vect,Rslt))>=13 where rngNameIndex is the position of the selected name from the list of names

[paul_pearson]

Hi Excel Fox

Thanks works great

Is post #17 possible with VBA code ..

Thanks

Paul

[Excel Fox]

Why do you want VBA when it's working fine with Conditional Format?

[Excel Fox]

It doesn't 'need' VBA to do any of those.

[paul_pearson]

Hi Excel fox

How would you do this without VBA.The picture when it inserts automatically sizes to the merges cells.

I am interested if this is achievable without VBA

Thank you

Paul

[Excel Fox]

Moved post to http://www.excelfox.com/forum/f2/lookup-picture-using-formula-without-using-vba-1042/