Non-macro method (like conditional formatting) to insert text in cell?

[Ed from AZ]

(XL 2007) Currently, we use conditional formatting to change cell
colors when a certain cell value is selected. The colors alert use
that this item needs special atention. The colors, though, can make
it difficult for some people to read the text.

For example, column A has report numbers. If the user selects
"Urgent" in Col B, the report number cell turns red. If he selects
"Completed", it turns blue. If it needs revising, it turns green.

I'm wondering if there is a way that a "text flag" can be inserted
into a cell using a non-macro method like contional formatting.

The idea is that if the user selected "Urgent, the report number would
show up with a "(U)" at the end. Needs revision would show ther
report number with "(R)".

I know you can't do this with conditional formatting, but I would love
it if there was a way that didn't use macros and was as simple to use.


Ed

 

[Jim Cone]

Re: "The colors, though, can make it difficult for some people to read the text."

Maybe I am missing something here,
but conditional formatting allows one to change the cell interior color and the font color.
Why not just specify a contrasting font color (using bold font) for the cell.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware

..
..
..

(XL 2007) Currently, we use conditional formatting to change cell
colors when a certain cell value is selected. The colors alert use
that this item needs special atention. The colors, though, can make
it difficult for some people to read the text.

For example, column A has report numbers. If the user selects
"Urgent" in Col B, the report number cell turns red. If he selects
"Completed", it turns blue. If it needs revising, it turns green.

I'm wondering if there is a way that a "text flag" can be inserted
into a cell using a non-macro method like contional formatting.

The idea is that if the user selected "Urgent, the report number would
show up with a "(U)" at the end. Needs revision would show ther
report number with "(R)".

I know you can't do this with conditional formatting, but I would love
it if there was a way that didn't use macros and was as simple to use.


Ed

 

[Ed from AZ]

Re:  "The colors, though, can make it difficult for some people to readthe text."

Maybe I am missing something here,
but conditional formatting allows one to change the cell interior color and the font color.  
Why not just specify a contrasting font color (using bold font) for the cell.

Well, Jim, I agree that would make sense.
But people love their plain black text on a white worksheet.
They "do not want to see a rainbow!"

The text flag idea was floated, so I'm asking about it.
Ed

 

[Jim Cone]

Incorporate your CF formula into the formula that determines the report number.


"Ed from AZ" <[email protected]>
wrote in message

Re: "The colors, though, can make it difficult for some people to read the text."

Maybe I am missing something here,
but conditional formatting allows one to change the cell interior color and the font color.
Why not just specify a contrasting font color (using bold font) for the cell.

Well, Jim, I agree that would make sense.
But people love their plain black text on a white worksheet.
They "do not want to see a rainbow!"

The text flag idea was floated, so I'm asking about it.
Ed

 

[Ed from AZ]

Incorporate your CF formula into the formula that determines the report number.

I'm not catching what you're thinking of, Jim.

The report number is just an incrementing number that's maunally
entered into our report program. So A5 has the number "1", A6 has
"=A5+1", and so forth.

How would that work into a CF formula?
Ed

 

[Gord Dibben]

Do you want to use a third hidden column with the report numbers?

If so, enter this formula in A1

=IF(B1="urgent",F1&"",IF(B1="completed",F1&"[C]",F1&"[R]"))

Assumes you have 3 options for B1

urgent, completed, revision needed

Column F contains the report numbers...............addjust to suit.


Gord Dibben MS Excel MVP

 

[Pete_UK]

Put this formula in A5:

=1&SUBSTITUTE(" ("&LEFT(UPPER(B5))&")"," ()","")

and this in A6:

=(IF(ISNUMBER(SEARCH(" ",A5)),LEFT(A5,SEARCH("
",A5)-1),A5)+1)&SUBSTITUTE(" ("&LEFT(UPPER(B6))&")"," ()","")

which can then be copied down as far as you need it to.

These formulae will pick up the initial letter of any text typed into
column B and add that to the number in column A, but will allow the
numbers to be incremented, like this:

1
2 (U) urgent
3
4 (C) completed
5
6 (R) Revised
7
8
9 (U) Urgent

I think that is what you are looking for, and with this you can
dispense with your conditional formatting.

Hope this helps.

Pete

 

[Ed from AZ]

Sorry for the delay in replying.
Holiday weekend in the US.

Pete:
Using SUBSTITUTE works great!

Gord:
Thanks for the suggestion of the extra column. I'd probably put it
on a hidden sheet, though, probably in a named range.

Ed