Number to equal cell value

[tremalking]

Hi all,
I currently, manually create my production work orders using excel (in-house ERP system not good enough).
Rather than manually type in the names of products to be produced, I would like to have the number 1 = a production item and 2 = another and so on.....
So basically when I type 1 in a cell it will automatically display a production item.

I imagine this to be a process where i would type all the names of my production items in individual cells, then create a formula to make 1 = a specific cell, 2, 3, 4, etc.....

Is this possible?

Your help is appreciated.

 

[Snakehips]

tremalking,

Welcome to MrExcel.

You are wanting to to use some variation of Lookup function.
Here is a link to just one of thousands of video tutorials that you will find on YouTube.

VLOOKUP Function Beginner to Advanced 26 Examples: How To Use Excel VLOOKUP Function - YouTube

Hope that helps.

 

[jim may]

Given a Sheet1 looking like this:

Excel 2010

	A	B	C	D	E	F	G
1
2			<< Enter your Number Here			1	Peter
3						2	Paul
4						3	Mary

<tbody>
</tbody>

Sheet1​

Paste this code into your worksheet's code module
Then enter either 1, 2 or 3 into Cell B2

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$2" Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Target = WorksheetFunction.VLookup(Target.Value, Range("$F$2:$G$4"), 2, False)
Application.EnableEvents = True
On Error GoTo 0
End Sub

 

[tremalking]

Hi Jim,
I cant seem to get this to work.
I have changed the cell ranges to suite my worksheet, but every time I enter a number in the cell only the number appears.
I really am a noob when it comes to Macros.

 

[jim may]

What is the Name of the Sheet your data is on? I assumed Sheet1

From the Sheet do an ALT+F11 to get to the VBE,
There in the left pane make sure your Sheet1 is highlighted -- then in the Code widow to the right - Paste in the entire block of Code!!

Return to the Spreadsheet and in cell b2 begin entering 1, 2, or 3

 

[tremalking]

Thanks very much for that Jim.
The reason I couldn't get it to work on other consecutive cells was the <> symbols on the second line rather than =
All sorted, I can now play about with this now.
Fantastic stuff!!
Again, a big THANKS!! Very much appreciated.