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

Is it possible to write a Vlookup which can be dragged down, so the
name of the file that it goes to get the information from is linked
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,
David


--
DD1
------------------------------------------------------------------------
DD1's Profile: http://www.excelforum.com/member.php...o&userid=25169
View this thread: http://www.excelforum.com/showthread...hreadid=394829

Hi!

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

Still interested?

Biff

"DD1" wrote in message
...

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

Is it possible to write a Vlookup which can be dragged down, so the
name of the file that it goes to get the information from is linked
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,
David


--
DD1
------------------------------------------------------------------------
DD1's Profile:
http://www.excelforum.com/member.php...o&userid=25169
View this thread: http://www.excelforum.com/showthread...hreadid=394829

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

Thankyou.


--
DD1
------------------------------------------------------------------------
DD1's Profile: http://www.excelforum.com/member.php...o&userid=25169
View this thread: http://www.excelforum.com/showthread...hreadid=394829

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.

--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"DD1" wrote in message
...

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

Is it possible to write a Vlookup which can be dragged down, so the
name of the file that it goes to get the information from is linked
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,
David


--
DD1
------------------------------------------------------------------------
DD1's Profile:
http://www.excelforum.com/member.php...o&userid=25169
View this thread: http://www.excelforum.com/showthread...hreadid=394829