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