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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,817
Messages
5,772,456
Members
425,760
Latest member
paphon

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