Replace the Find Function with a formula

[CHARLENE]

Is it possible to create a formula that is like the find function. I would
like the user to be able to type the number or name of an item they are
looking for and it will take them to the cell that has that name or number in
it.

Also, if there is, what happens when there are mutiple cells with the same
reference?

thanks
Charlene

 

[ilia]

Not possible quite as you describe. You can use this VBA event:

Private Sub Worksheet_Change(ByVal Target As Range)
Const myCell As String = "A1"

If Not Intersect(Target, Me.Range(myCell)) Is Nothing Then
Me.UsedRange.Find(Me.Range(myCell).Value).Select
End If
End Sub

Whenever cell A1 is changed, the value in A1 is searched and first
found instance of it is selected. There isn't a real way to locate
multiple instances, per your process design. You can, however, modify
the Find method arguments to look by rows, columns, etc - just like
you can with the built-in Find functionality.