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

[Guest]

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.

 

[Dave Peterson]

The worksheet function that you'd want to use is:

=indirect()

Then build the string that represents the address (workbook/worksheet/cell) to
bring back.

The bad news is that =indirect() doesn't work with closed files.

The good news is that Harlan Grove wrote a userdefined function called =Pull()
that will do what you want.

http://www.google.com/[email protected]

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

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.

 

[Guest]

Thanks Dave,

I'll try it out. I did have a brainwave, and thopught to rename all the
workbooks A1.xls,A2.xls etc, but that didn't work...

Cheers

The worksheet function that you'd want to use is:

=indirect()

Then build the string that represents the address (workbook/worksheet/cell) to
bring back.

The bad news is that =indirect() doesn't work with closed files.

The good news is that Harlan Grove wrote a userdefined function called =Pull()
that will do what you want.

http://www.google.com/[email protected]

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

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.