Can I type text in one cell and have a value auto-enter in 2nd cel

[Guest]

What I would like to accomplish is that if I enter a certain word or phrase
in a cell, I want a pre-determined number to automatically enter into another
cell. For example...if I type "Green" into one cell I want the number "2" to
be automatically enter into another cell in the same row. For this specific
task, I will be entering any of 30 different words that correspond to 10
different numbers, but will be doing this almost a thousand times throughout
the spreadsheet....the ability to have the number auto-entered would be a
great time saver. Any advice would be greatly appreciated.

 

[Guest]

You could use VLOOKUP. Set up a table, such as

A B
1 Green 2
2 Brown 3
3 Red 2


Then let's say you enter "Green" in cell D1, in E1 enter
=VLOOKUP(D1,$A$1:$B$3, 2, 0)

and copy down column E as far as needed.

 

[Guest]

Right-click on the sheet in question's tab and choose 'View code'. Where the
cursor's flashing in the window that open paste the following:

Private Sub Worksheet_Change(ByVal Target As Range)
'can be adjusted to respond only to certain cells being changed.
'can be adjusted to work in all sheets of the workbook
Application.EnableEvents = False
Select Case Target.Value
Case "this"
Y = 1.1
Case "is"
Y = 2.2
Case "really"
Y = 3.3
Case "getting"
Y = 4.4
Case "boring"
Y = 5.5
Case "cannot"
Y = 6.6
Case "think"
Y = 7.7
Case "of"
Y = 8.8
Case "anything"
Y = 9.9
Case "more"
Y = 10.1
Case Else
Y = "not valid" 'remove to disable and uncomment next line
'Application.EnableEvents = True: Exit Sub
End Select
'next line places Y in cell 2 to the right
Target.Offset(0, 2) = Y
'next line capitalises first letter of word, comment out if not required
Target.Value = UCase(Left(Target.Value, 1)) & Mid(Target, 2)
Application.EnableEvents = True
End Sub

Close the window (don't need to save - it'll save when you save the
workbook) and see if it does what you want. It should enter a number two
cells to the right of any cell on the sheet if you enter any one of the
following words: this is really getting boring cannot think of anything more.
It should enter 'not vald' if you type anything else in the cell.

 

[Guest]

Thanks for the help. This was exactly was I was looking for and should shave
a lot of time off my data entry for this project and for numerous ones in the
future.

 

[Guest]

You're welcome - thanks for the feedback.

Thanks for the help. This was exactly was I was looking for and should shave
a lot of time off my data entry for this project and for numerous ones in the
future.