vlookup macro or extract info?

jaypatel

Active Member
Joined
Nov 25, 2002
Messages
385
Hi,

I have a database called invoices.xls, and i have about 20 opened spreadsheets at the same time (like B0011.xls, B0025.xls, C0036.xls etc).

The set up in the invoice file is like so:

col a is account number (like B0011) etc
col b is invoice number
col c is amount
col d is date

is it possible that a macro can say look at column a ie b0011, and whatever is on the same row for b0011 to extract to the spreadsheet b0011.xls

example,

a1 = D0011, b1 = Test1, c1 = 45.41, d1 = 28/05/03, so this b1-d1 is extracted to spreadsheet D0011.

a2 = G0123, b2 = test2, c2 = 452.87, d2 = 25/11/03, so b2-d2 is extracted to spreadsheet G0123

they should be extracted to named ranges.... so the invoice number is extracted to range "invoice", amount extracted to range "amount" and date extracted to range "date".

Jay
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

jimboy

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
How have you set up your named range in the wb you want the data to be copied to? - If you have selected a range (say A1:A20) and named it, how is the new data going to be added is you have data in all of those cells.

It would be easier to add the data to the bottom of the column, what columns is you data in? i.e. invoice is in col. A, amount col. B etc.
 

jaypatel

Active Member
Joined
Nov 25, 2002
Messages
385
the range names are 1 cell in the workbook i want it copied..... ie cell called invoice in the target file is f43. so range name called invoice is copied to range name invoice in target file: orginal.xls!invoice to target.xls!invoice

Jay
 

jimboy

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
jaypatel said:
the range names are 1 cell in the workbook i want it copied..... ie cell called invoice in the target file is f43. so range name called invoice is copied to range name invoice in target file: orginal.xls!invoice to target.xls!invoice

Jay

I'm lost, can you post some data on the sheet and explain?
 

jaypatel

Active Member
Joined
Nov 25, 2002
Messages
385
Jimboy, what is your email address please..... and I will send all the necessary stuff.

Jay
 

Watch MrExcel Video

Forum statistics

Threads
1,113,794
Messages
5,544,322
Members
410,603
Latest member
rseckler
Top