You would have to have a list of product codes next to their product somewhere on the sheet.

For example:

Z1 = JAF
AA1 = Jaffa Cakes
Z2 = SPR
AA2 = Space Raiders
Z3 = BUT
AA3 = Butter

In VBA you write a worksheet_SelectionChange Function

Dim myRow as Integer
Dim myColumn as Integer
Dim myCode as String

Application.ScreenUpdating = False
myCode = ActiveCell.Offset(-1, 0).Text 'The Cell with the code in

myRow = 0
myColumn = 26 'Column where codes are stored
Do
myRow = myRow+1
If Cells(myRow, myColumn).Text = myCode Then

*confused*

Use the VLOOKUP function

Cell A1 use for the product Code and use Cell B1 as the name to be returned

Set up a table with 2 columns (I use the example E and F)

Then in B1 enter the formula
=VLOOKUP(A1,E1:F99,2,FALSE)

this will look for the value in A1 in the leftmost column of E and F (i.e. in the E column) and return the second column (2) in the range E and F thereby returning a value in F when matched. FALSE reurns N/A if an exact match is not found.

Originally posted by Xymbiant
I am working on this excel project with someone and I am trying to figure out something. Here is the lowdown. You have column 'A' and column 'B'. If someone enters in a product code into column 'A', column 'B' is suppose display the name of that product. How would I go about setting this up? There are multiple products and I think that this is possible to do via Visual Basic module. So, can you please help me?

using the VLOOKUP or HLOOKUP function your source (list of product codes and names) can be any where in the XLS file--any sheet.

It could also be in another XLS file but generally I wouldn't recommend using Excel for something so complex as to need to store it in a separate file.

=Sheet4!C9+Sheet2!E15*Ledger!B395

So if your codes were in Sheet2
=VLOOKUP(A1,Sheet2!E1:Sheet2!F99,2,FALSE)

You could also rename the sheet to something easier to recall, say "ProductCodes"
=VLOOKUP(A1,ProductCodes!E1:ProductCodes!F99,2,FAL SE)

this is probably better than putting it to the right of the sheet you are working on -- if you later add columnshaving it in the same sheet can complicate things.

Mark