Conditional Formatting to format a number with a preceding letter

[doverfelt]

I have an issues & actions log i'm setting up. In the log, I have Column A prefilled ID # field; 1, 2...50 to start. Then Column B is a dropdown with values of Action or Issue.

Based on the value in column B (Action or Issue), I would like add either an A- or an I- in front of the number automatically. So... it would look like this...

Column A Column B
A-1 Action
I-2 Issue
I-3 Issue
A-4 Action

Is this remotely possible? I thought it'd be tied to custom Text string, but i'm not seeing the link between the formula to result in using a specific custom text.

Thanks for any ideas.

Darren

 

[Fluff]

If you want to change the value of col A you'll need VBA, but if you just want to display A-1 & leave the cell contents as 1, you can use CF
Select the range in col A you want this to work on > Home Tab . Conditional formatting > New rule > Use a formula > enter =B2="Action" > Format > Number > Custom > "A-"# > Ok
and repeat but for Issue

 

[doverfelt]

Thank you, Fluff! This makes perfect sense. But, i did this and the formatting of col A is not taking. Not sure why at this point... may have to do some reading, I guess...

 

[Fluff]

The "Action" is just the value in Col B.
Also should have said to change B2 to match the first cell in col A in the applies to range. So if you select A5:A100 the formula should be =B5="Action"

 

[doverfelt]

Gotcha, Fluff. thanks. The text in the field is just not working. I added a fill color to each rule, red fill for Issue and green fill for Action. This let me make sure the formula itself was working. So, selecting Issue or Action in Column B changes Column A to the assigned fill color. The thing not working is the cell formatting the A- or the I- in front of the number... still plugging away on it, trying various custom number formats

 

[Fluff]

The custom format of "A-"# works for me, are the values in col A numbers?