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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
 
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.
 
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.
 
You're welcome - thanks for the feedback.

IowaTBone said:
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.
Click to expand...
 
Back
Top