Filling a cell for Data Entry

[bassbone]

I have a spreadsheet where I have approx. 50 product codes. When I select a product code, I want Excel to fill in the cost (from a list on a separate sheet) in the cell next to the product code. In the past I've used nested "IF" statements, but I have what appears to be more ifs than exel can handle.

Thanks for the help

[Brian from Maui]

Try using VLOOKUP where
Column A has your product code
Column B your cost adjacent to the product code.

=vlookup(C1,range,2)

Where C1 is where you enter your product code

[James]

Hi BassBone,

If you want to do this with vba:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As _ Excel.Range)
Dim Code As Integer

If Target.Cells.Count > 1 Then End
If Target.Row >= 4 Then
If Target.Column = 2 Then
code = Target
Application.EnableEvents = True
Sheets("Sheet2").Range("B5").Offset(code, 0) - Resize(1, .Copy _ Destination:=Target.Offset(0, 1)
Target.Offset(1, 0).Activate
End If
End If
Application.EnableEvents = True
End Sub

James