Conditional Formatting, Concatenation, Indirect, Highlightin, any thoughts ...

johnboston

New Member
Joined
Mar 11, 2011
Messages
23
Conditional Formatting, Concatenation, Indirect, Highlightin, any thoughts ...


Hi,

I am trying to do conditionl formatting based on concatenation in a cell. Please allow me to give an example.

Cell B36 contains a concatenated total based on entries throughout the workbook of Dogs, Cats, and Mules which displays as "x-x-x" ... that works
fine ... I'm fine with this ...

My problem is I want cell B36 to highlight if the total is anything other than
2-1-0
3-0-0
3-0-1
3-0-2
3-1-0

Presently I'm using the following conditional formatting, with the highlighted colors in brackets ... the brackets and color listed are placed
here in the post only, they are not part of the formula. I already see problems in the formulas as presented but am frustrated and looking for help ... I'm sure there is a better way to do this than the way I'm attempting ...

=INDIRECT("$b"&ROW())>"2-1-1"<"3-1-1" [yellow]
=INDIRECT("$b"&ROW())<="2-0-9" [blue]
=INDIRECT("$b"&ROW())>="3-1-1" [brown]

For the most part this works well, until I get a value like "2-3-2" in which no highlighting occurs ... to me this ought to appear yellow (or
brown, in the case of "3-1-1") because it is in between "2-1-1" and "3-1-1" ...

I'm not sure that INDIRECT is even the right function ...

Any ideas on how to make this work better would be greatly appreciated ...

Sincerely,

John
 
Last edited:

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Excelestial

Well-known Member
Joined
Jan 2, 2009
Messages
828
You are asking Excel to do mathematical logic on alpha characters. Not possible. In the mathematical world, 232 IS between 211 and 311. However, it evidently is not the case in the text world. Text is assigned values...Excel evaluates the worth of text according to the value of the text string.
 

johnboston

New Member
Joined
Mar 11, 2011
Messages
23
Any thoughts on how to approach differently the task in order to achieve what I'm trying?

You are asking Excel to do mathematical logic on alpha characters. Not possible. In the mathematical world, 232 IS between 211 and 311. However, it evidently is not the case in the text world. Text is assigned values...Excel evaluates the worth of text according to the value of the text string.
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

Conditional Formatting, Concatenation, Indirect, Highlightin, any thoughts ...


Hi,

I am trying to do conditionl formatting based on concatenation in a cell. Please allow me to give an example.

Cell B36 contains a concatenated total based on entries throughout the workbook of Dogs, Cats, and Mules which displays as "x-x-x" ... that works
fine ... I'm fine with this ...

My problem is I want cell B36 to highlight if the total is anything other than
2-1-0
3-0-0
3-0-1
3-0-2
3-1-0

Presently I'm using the following conditional formatting, with the highlighted colors in brackets ... the brackets and color listed are placed
here in the post only, they are not part of the formula. I already see problems in the formulas as presented but am frustrated and looking for help ... I'm sure there is a better way to do this than the way I'm attempting ...

=INDIRECT("$b"&ROW())>"2-1-1"<"3-1-1" [yellow]
=INDIRECT("$b"&ROW())<="2-0-9" [blue]
=INDIRECT("$b"&ROW())>="3-1-1" [brown]

For the most part this works well, until I get a value like "2-3-2" in which no highlighting occurs ... to me this ought to appear yellow (or
brown, in the case of "3-1-1") because it is in between "2-1-1" and "3-1-1" ...

I'm not sure that INDIRECT is even the right function ...

Any ideas on how to make this work better would be greatly appreciated ...

Sincerely,

John
That highlighted formula is not well formed.

Something like this should work...

=AND(cell_reference>="2-1-1",cell_reference<="3-1-1")

Replace "cell_reference" with your INDIRECT stuff.
 

johnboston

New Member
Joined
Mar 11, 2011
Messages
23
I will try it ... will not be able to try for a short while ... hope it works, thanks ... will let you know one way or the other ...

That highlighted formula is not well formed.

Something like this should work...

=AND(cell_reference>="2-1-1",cell_reference<="3-1-1")

Replace "cell_reference" with your INDIRECT stuff.
 

johnboston

New Member
Joined
Mar 11, 2011
Messages
23

ADVERTISEMENT

Did not work on first try ... will try again, may have entered it incorrectly ...
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Did not work on first try ... will try again, may have entered it incorrectly ...
<b>Sheet1</b><br /><br /><table border="0" cellspacing="0" cellpadding="0" style="font-family:Verdana,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:72px;" /><col style="width:72px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:left; border-style:solid; border-width:1px; border-color:#000000; ">2-3-2</td><td style="text-align:right; border-top-style:solid; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">TRUE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:left; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">0-0-0</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">FALSE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:left; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">4-9-3</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">FALSE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:left; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">1-8-5</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">FALSE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:left; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">2-1-2</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">TRUE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:left; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">3-0-0</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">TRUE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:left; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">8-8-8</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">FALSE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:left; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">9-2-1</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">FALSE</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:left; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">2-5-0</td><td style="text-align:right; border-right-style:solid; border-bottom-style:solid; border-width:1px; border-color:#000000; ">TRUE</td></tr></table> <br /><br />
This formula entered in B2 and copied down:

=AND(A2>="2-1-1",A2<="3-1-1")
 

johnboston

New Member
Joined
Mar 11, 2011
Messages
23
Biff,

Thank you this seems to help, so far ... gives me some ideas ...

Thanks,

John
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top