Excel 97/2000 - Help with looking up external data.
 

Hi,

Can someone please help me with a formula that I am having problems creating?

In a folder I have 500+ 'randomly' named workbooks, plus one master
workbook. The master workbook stays the same (it is the one I work with)
whilst all the others will change daily. The changing workbooks all contain
the same information types - that is each one is a set of contact details,
with the file name being the contact name and the following example data:

A1 - Address 1
A2 - Address 2
B1 - Telephone
B2 - email address

etc.

On a daily basis, the master workbook is to collect all this data, and
export it as a single file/table for upload to a contact database, then the
next day another 500 or so files are substituted for those already processed.

My problem is this: I know all the individual file names, and enter them as
a list in column A of the master workbook. I then want A2 to look up the data
from A1 in workbook [A1], A3 to look up A2 in workbook [A1] etc, with each
row in the master workbook being a 'record' of all the data in workbook [A1]

I understand how VLOOKUP and HLOOKUP work, but what I am trying to do is an
EXTERNAL LOOKUP. The long way round is to type =Filename,Reference etc into
each cell or perhaps insert a link to each cell in each workbook, but clearly
this is not practical. But if I enter ='A1.xls!'$A$1 the formula looks for a
workbook called A1.xls.

How can I get the formula to look for a workbook named (value A1.xls) rather
than A1.xls

Any help (and formulae) greatly appreciated.

Thanks in advance.

I hasten to add that the master workbook and the 'variable' workbooks are all
in the same folder - so the reference only needs to look 'outside' itself.

Thanks

"Adam Harris" wrote:

Hi,

Can someone please help me with a formula that I am having problems creating?

In a folder I have 500+ 'randomly' named workbooks, plus one master
workbook. The master workbook stays the same (it is the one I work with)
whilst all the others will change daily. The changing workbooks all contain
the same information types - that is each one is a set of contact details,
with the file name being the contact name and the following example data:

A1 - Address 1
A2 - Address 2
B1 - Telephone
B2 - email address

etc.

On a daily basis, the master workbook is to collect all this data, and
export it as a single file/table for upload to a contact database, then the
next day another 500 or so files are substituted for those already processed.

My problem is this: I know all the individual file names, and enter them as
a list in column A of the master workbook. I then want A2 to look up the data
from A1 in workbook [A1], A3 to look up A2 in workbook [A1] etc, with each
row in the master workbook being a 'record' of all the data in workbook [A1]

I understand how VLOOKUP and HLOOKUP work, but what I am trying to do is an
EXTERNAL LOOKUP. The long way round is to type =Filename,Reference etc into
each cell or perhaps insert a link to each cell in each workbook, but clearly
this is not practical. But if I enter ='A1.xls!'$A$1 the formula looks for a
workbook called A1.xls.

How can I get the formula to look for a workbook named (value A1.xls) rather
than A1.xls

Any help (and formulae) greatly appreciated.

Thanks in advance.