vlookup macro or extract info?

[jaypatel]

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

 

[jimboy]

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]

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]

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]

Jimboy, what is your email address please..... and I will send all the necessary stuff.

Jay