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.


"IowaTBone" wrote:

> 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.

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.

--
p45cal


"IowaTBone" wrote:

> 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.

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.

"JMB" wrote:

> 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.
>
>
> "IowaTBone" wrote:
>
> > 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're welcome - thanks for the feedback.

"IowaTBone" wrote:

> 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.
>
> "JMB" wrote:
>
> > 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.
> >
> >
> > "IowaTBone" wrote:
> >
> > > 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.