Automatic lookup in another file

JamesKM

Board Regular
Joined
Aug 12, 2002
Messages
130
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)
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
Hi:


Would something like this work?
Book2.xls
CDEFGHI
1
2Data base info (in folder: james and worksheet lookup.xls
3ref no.co name
4111Xerox Co.
5222Kodac Inc
6333Peter Pan flyers
7444Mom n' pop stores
8
9
10
11
12Lookup
13111Xerox Co.
14
15
16=VLOOKUP(D13,'C:\james\[lookup.xls]Sheet1'!$D$10:$E$14,2)
17
18
Sheet1


hope this helps
plettieri
 

JamesKM

Board Regular
Joined
Aug 12, 2002
Messages
130
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,577
Messages
5,596,996
Members
414,116
Latest member
sfullnet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top