Vlookup - name of file to get info from is in Column A

D

DD1

I have a file with data in Column A only. For example, cell A1 contain
the word "Pathology", A2 contains the word "Pharmacy" etc. These name
are actual filenames. So I have a file called Pathology.xls and on
called Pharmacy.xls. I would like to write a vlookup to extrac
information from these files, and put this information in Column B.

Is it possible to write a Vlookup which can be dragged down, so th
name of the file that it goes to get the information from is linke
directly to the name in Column A?

Within the Vlookup function - it's the second part of the formula
after the first comma, that I want to link to Column A

I hope that makes sense.
Regards,
Davi
 
B

Biff

Hi!

There is a way but it requires that the target file be open. That's a huge
disadvantage!

Still interested?

Biff
 
D

DD1

Yes - I don't think that's a big problem. I've got about 650 files i
total, but they are all in the same directory - and if it turns ou
that memory is an issue, I can open 50 or so at a time. They are quit
small files.

Thankyou
 
A

Arvi Laanemets

Hi

As there are only 2 external workbooks you want to retrieve information of,
and both of them have fixed names, then there is a way to avoid the source
files to be opened. Simply in your file, to where you want to retrieve data,
create 2 hidden sheets which mirror needed info, and in your vlookup(s),
refer to those sheets. You can even use INDIRECT to get data from selected
file.

I.e. create sheets p.e. Pathology and Pharmacy.

On sheet Pathalogy, into cell A1 enter the formula (I assume needed
information in Pathology.xls is on sheet p.e. Data)
=IF('C:\My Documents\[Pathology.xls]Data'!A1="","",'C:\My
Documents\[Pathology.xls]Data'!A1)
and copy to range, big enough to include all data in source table. (when
there are columns not needed in lookup, you can delete them from mirror
sheet after that - to avoid the file to be bloated with abundant formulas)

Similarily mirror needed data from Pharmacy.xls on sheet Pharmacy.

Define mirrored tables on sheets Pathology and Pharmacy as Dynamic named
ranges Pathology and Pharmacy. (My advice is to hide both mirror sheets
after that.) Now you can have VLOOKUP formuals like
=VLOOKUP(A1,Pathology,2,0);
=VLOOKUP(A1,INDIRECT(Setup!$B$1),2,0)
where the entry in cell Setup!$B$1 is either "Pathology" or "Pharmacy";
=IF(Setup!$B$1="Pathology",VLOOKUP(A1,Pathology,2,0),VLOOKUP(A1,Pharmacy,2,0));
etc.
 
Top