Thanks. INDIRECT did the trick.

"Keyur" wrote:


following is a code i am using in one of my spreadsheet to do similar
thing. the only difference might the fact that i am retreiving same
cell (A3) form each file.


Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim flnm As String
Application.EnableEvents = False
If Target.Column = 1 And IsEmpty(Target.Value) = False Then
flnm = "C:\Documents and Settings\kapatel\Desktop\" & Target.Value & ".xls"
If Dir(flnm) < "" Then
Me.Cells(Target.Row, 2).Formula = "='C:\Documents and Settings\kapatel\Desktop\[" & Target.Value & ".xls]Details'!$A$3"
Else
Me.Cells(Target.Row, 2).Value = "File not found."
End If
ElseIf IsEmpty(Target.Value) = True Then
Me.Cells(Target.Row, 2).Value = "File not found."
End If
Application.EnableEvents = True
End Sub

--------------------


Hope this helped

Keyur


--
Keyur
------------------------------------------------------------------------
Keyur's Profile: http://www.excelforum.com/member.php...fo&userid=7786
View this thread: http://www.excelforum.com/showthread...hreadid=385705