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:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Did not work on first try ... will try again, may have entered it incorrectly ...
Book1
AB
22-3-2TRUE
30-0-0FALSE
44-9-3FALSE
51-8-5FALSE
62-1-2TRUE
73-0-0TRUE
88-8-8FALSE
99-2-1FALSE
102-5-0TRUE
Sheet1

This formula entered in B2 and copied down:

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

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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
Back
Top