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
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: