conditional formating issue.

[needhelp2]

Hi Guys,

Today i am creating analysis report.i am facing a little problem in that so i need your kind assistance.Kindly respond in this regard.

I have Range from C3:F3 represent as days.
In C3:F3 i have placed name of my sales person who works during day 1-4.
I specify name Jhon in A3 as primary person for that range C3:F3

What i needs to know if jhon moves in all these days then B3 shows value is true,other if any single days person name change then it shows false.

e.g
A3 B3 C3 D3 E3 F3
Jhon True Jhon Jhon Jhon Jhon

Adam False Adam Jhon Jhon Adam

Kinldy tell me how i can manage it?


BR
Smith

 

[markmzz]

Try this:

Note: VERDADEIRO = TRUE and FALSO = FALSE


<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Paul</td><td style="text-align: right;;">VERDADEIRO</td><td style=";">Paul</td><td style=";">Paul</td><td style=";">Paul</td><td style=";">Paul</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Mary</td><td style="text-align: right;;">FALSO</td><td style=";">Mary</td><td style=";">Milka</td><td style=";">Mary</td><td style=";">Mary</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Scott</td><td style="text-align: right;;">FALSO</td><td style=";">Scott</td><td style=";">Scott</td><td style=";">Scott</td><td style=";">Paul</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Klein</td><td style="text-align: right;;">VERDADEIRO</td><td style=";">Klein</td><td style=";">Klein</td><td style=";">Klein</td><td style=";">Klein</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">C3:F3<>A3</font>)</font>)=0</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[needhelp2]

waoooo that's great dear markmzz.
It work same as i need,Thanks you very much.

 

[markmzz]

waoooo that's great dear markmzz.
It work same as i need,Thanks you very much.

You're welcome and thank you for the feedback.

Markmzz

 

[needhelp2]

Dear can you tell me how i can use same formula to ignore blank cell as well in same sheet,mean if in d3 cell is blank then there wont be any effect on result.

 

[markmzz]

Dear can you tell me how i can use same formula to ignore blank cell as well in same sheet,mean if in d3 cell is blank then there wont be any effect on result.

Try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Paul</td><td style="text-align: right;;">VERDADEIRO</td><td style=";">Paul</td><td style="text-align: right;;"></td><td style=";">Paul</td><td style=";">Paul</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Mary</td><td style="text-align: right;;">FALSO</td><td style=";">Mary</td><td style=";">Milka</td><td style=";">Mary</td><td style=";">Mary</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Scott</td><td style="text-align: right;;">FALSO</td><td style=";">Scott</td><td style=";">Scott</td><td style=";">Scott</td><td style=";">Paul</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Klein</td><td style="text-align: right;;">VERDADEIRO</td><td style=";">Klein</td><td style=";">Klein</td><td style=";">Klein</td><td style=";">Klein</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">C3:F3<>A3</font>)-(<font color="Red">C3:F3=""</font>)</font>)=0</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[needhelp2]

Try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Paul</td><td style="text-align: right;;">VERDADEIRO</td><td style=";">Paul</td><td style="text-align: right;;"></td><td style=";">Paul</td><td style=";">Paul</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Mary</td><td style="text-align: right;;">FALSO</td><td style=";">Mary</td><td style=";">Milka</td><td style=";">Mary</td><td style=";">Mary</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Scott</td><td style="text-align: right;;">FALSO</td><td style=";">Scott</td><td style=";">Scott</td><td style=";">Scott</td><td style=";">Paul</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Klein</td><td style="text-align: right;;">VERDADEIRO</td><td style=";">Klein</td><td style=";">Klein</td><td style=";">Klein</td><td style=";">Klein</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">C3:F3<>A3</font>)-(<font color="Red">C3:F3=""</font>)</font>)=0</td></tr></tbody></table></td></tr></table><br />
Markmzz

Fabulous!Thanks Markmzz.

 

[markmzz]

Fabulous!Thanks Markmzz.

Thanks for the feedback.

Markmzz