Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Adam Harris
 
Posts: n/a
Default 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.

  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

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/groups?selm=sH...wsranger. com

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

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.


--

Dave Peterson
  #3   Report Post  
Adam Harris
 
Posts: n/a
Default

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

"Dave Peterson" wrote:

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/groups?selm=sH...wsranger. com

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

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.


--

Dave Peterson

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I export data from Excel into an ODBC client / or plain tex Margaret Excel Discussion (Misc queries) 2 February 15th 05 10:51 PM
How do I create a list in excel that contains external data? bill@bb Excel Discussion (Misc queries) 1 February 15th 05 03:45 AM
How do i copy columns of data in notepad into microsoft excel? JP New Users to Excel 2 February 10th 05 10:47 PM
copying data from Excel spreadsheet to another DavidB Excel Discussion (Misc queries) 1 January 11th 05 12:26 AM
getting data from 2 excel sheets automatically pinar Excel Worksheet Functions 0 November 9th 04 12:47 PM


All times are GMT +1. The time now is 07:58 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"