Conditional Format Using Formula By Referring To Another Sheet

[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.