i have the following code added to the s/s tab which does what i want but is
there a way of adding this to cell like a formula thing like vlookup??

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 2 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
Target.Value = Worksheets("Station").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Station").Range("StationList"), 0), 0)
Application.EnableEvents = True
End If
End Sub

thanks pete

First, your code actually changes the value of the cell after you type in your
data.

You won't be able to do that using a formula (well, unless you type the formula
in that cell).

You might be able to use a formula in an adjacent cell to return that same
value, though.

Something like this in C7:
=OFFSET(Station!A1,MATCH(B7,StationList,0),0)

If that doesn't work, you may want to post the address of StationList. It'll
make testing a bit easier.


Little pete wrote:

i have the following code added to the s/s tab which does what i want but is
there a way of adding this to cell like a formula thing like vlookup??

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Column = 2 Then
If Target.Value = "" Then Exit Sub
Application.EnableEvents = False
Target.Value = Worksheets("Station").Range("A1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Station").Range("StationList"), 0), 0)
Application.EnableEvents = True
End If
End Sub

thanks pete

--

Dave Peterson