formula needed to extract data from another file

[Norbert]

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

 

[Javed]

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, alsofile
        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

The thing can be done by only XLM macro.Actuall i also needed this.Can
anyone help on XLM macro.

TIA

 

[Gary Keramidas]

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

 

[Gary]

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]

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

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


:

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
.