Automatic lookup in another file

[JamesKM]

Please could someone help with this. I think I need a worksheet_change thing:
I want to automatically lookup any entry I put in column D ("Ref No.") in a spreadsheet stored elsewhere (preferebaly - say C:\James\Lookup.xls) and then return in column E the company name. (assume Ref No. is col A and Company Name is in Col B in "lookup.xls")?

(the reason I want it stored elsewhere is that it will be about 30,000 rows)

 

[plettieri]

Hi:


Would something like this work?

Book2.xls
	C	D	E	F	G	H	I
1
2	Data base info (in folder: james and worksheet lookup.xls
3		ref no.	co name
4		111	Xerox Co.
5		222	Kodac Inc
6		333	Peter Pan flyers
7		444	Mom n' pop stores
8
9
10
11
12		Lookup
13		111	Xerox Co.
14
15
16		=VLOOKUP(D13,'C:\james\[lookup.xls]Sheet1'!$D$10:$E$14,2)
17
18
Sheet1

hope this helps
plettieri

 

[JamesKM]

Thanks plettori, but not quite what I was after.
I could put in the vlookup as you suggest, but i would have to have the formula in about 10,000 cells, which would make the spreadsheet cumbersome.

I have this so far:
___________________________________
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 3 Then Exit Sub
If Target.Value = "" Then Exit Sub

Search = Target

With Worksheets("Accounts").Range("Accounts")
Set c = .find(Search, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not c Is Nothing Then
Target.Offset(0, 1) = Worksheets("Accounts").Range(c.Address).Offset(0, 1)
Target.Offset(0, 2) = Worksheets("Accounts").Range(c.Address).Offset(0, 2)
Target.Offset(0, 3) = Worksheets("Accounts").Range(c.Address).Offset(0, 3)
Target.Offset(0, 4) = Worksheets("Accounts").Range(c.Address).Offset(0, 4)
End If
End With

End Sub
______________________________________
This code searches for entries put into column C and returns various data I need in a lookup table in Sheet2 (called "Accounts").

However as there are 35,000 rows and 6 columns filled in the spreadsheet is already 4MB without any data in the important sheet.
What I was hoping to be able to do was to do the same search but in a different spreadsheet. I can use the macro editor and get most of the way there, but I am not sure what to do about the checking if the lookup spreadsheet is already open.