Ideas for adding text to a cell based on criteria.

[jusbgrub]

Column A has a pull-down menu with each cell that has various "Zones" to choose from. Column C has cells where numbers are entered. If for example, "Zone 1" is selected in A:2, is there a way to add the letters "TP" or "CD" to the beginning of the number in C:2 after it is typed based on what selection has been chosen in A:2?

 

[Joe4]

Welcome to the Board!

I think that will probably require VBA. Are you good with that?
If so, what exactly are your rules (what is your complete listing of values in your drop-down menu, and what prefix is associated with each)?

If you are unable to use VBA, you could use a "helper" column, i.e. enter the number in column C, and have a formula in column D that shows this number with its appropriate prefix.

 

[mumps]

add the letters "TP" or "CD"

You have to decide which of the two you want to add.
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a number in column C and press the ENTER key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target.Offset(, -2) = "Zone 1" Then
        Target = "TP" & Target
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[Ngamia]

If you are unable to use VBA, and presuming that your "pull-down menu" is a form control box. As Joe4 has said above, you can use a helper column to process the additional letter as show below.

Cell A1 contains the results of changing the values in the form control box. If you have a version newer than Excel 2019, you can use IFS to shorten the formula.

Excel Issue2.xlsx
	A	B	C	D	E	F
1	4		DATA ENTRY BOX	NEW VALUE	LIST OF ZONES	LETTER TO ADD
2			3333	BB3333	Zone 1	TP
3			4444	BB4444	Zone 2	CD
4			5555	BB5555	Zone 3	AA
5					Zone 4	BB
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=IF($A$1=1,$F$2&C2,IF($A$1=2,$F$3&C2,IF($A$1=3,$F$4&C2,IF($A$1=4,$F$5&C2,""))))

 

[jusbgrub]

Thanks for the response. Im "OK" with VBA. I made a sample sheet and uploaded a picture. I am just stumped on how to keep excel from just adding the prefix and wiping out the number.

 

[mumps]

Did you try the macro I suggested in Post #3?

 

[jusbgrub]

I did. It didn't work. The cells i gave are not representative of my actual sheet. I also need Zones 1 and 2 to be "CD" and Zones 3 and 4 to be "TP". On my actual sheet the pull downs are in E3:E30 and the numbers i need the prefix added to is G3:G30. This is a daily sheet and is cleared every day and filled out from top to bottom, left to right.

 

[jusbgrub]

I got this to work. Thank you very much for your help.



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 5 Then Exit Sub
If Target.Column <> 7 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
If Target.Offset(, -2) = "Cassioli" Then
Target = "CD" & Target
End If
If Target.Offset(, -2) = "Doorline" Then
Target = "CD" & Target
End If
If Target.Offset(, -2) = "Testing" Then
Target = "TP" & Target
End If
If Target.Offset(, -2) = "Packout" Then
Target = "TP" & Target
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub