formula needed to extract data from another file
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 . |
All times are GMT +1. The time now is 09:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com