Hi Gary,
sorry for not coming back. I couldn't get to do any work on that for the
last couple of days.
I've tried your code but unfortunately it is not exactely doing what I want.
I also might have to blame myself for not making me clearly understood,
therefore I try a new approach, with a new thread (retrieve data into a
spreadsheet from a copy of that spreadsheet which is saved as its own
xls file).

Regards, Norbert


On 16-05-10 01:26, Gary wrote:
didn't do a lot of testing, but give this a try.

on the sheet you want your values to be populated, right click the sheet
name and choose view code
paste the following code. change the fpath variable to your path.

try entering the file name, 4788 in your case in column A

Private Sub Worksheet_Change(ByVal Target As Range)
Dim fName As String
Dim fpath As String
fpath = "N:\My Documents\Excel\"
If Selection.Count = 1 And Target.Column = 1 Then
fName = Dir(fpath& Target.Value& ".xls")
If Len(fName) 0 Then
Target.Offset(, 1).Formula = "='"& fpath& "["& _
Target.Value& ".xls]Sheet1'!"& Target.Offset(,
1).Address
Else
Target.Offset(, 1).Value = ""
End If
End If
End Sub


"Norbert" wrote:


Hi,
is there any possibility to do the following:

e.g: in file program.xls, cell A1 I enter: 4788
in c:\My Documents\data folder there are many files, also file
4788.xls

I need a formula in program.xls, cell A2 which looks up the
value of cell D5 in file 4788.xls

Next time I will enter e.g. 3578 into A1 and the formula must
then give back the entry of D5 in file 3578.xls

Hope I made myself understandable.

Regards,
Norbert
.