If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Copy data between 2 Excel for comparison | Boba | General Discussion | 1 | February 18th, 2005 12:56 AM |
Pulling data from 1 sheet to another | Dave1155 | Worksheet Functions | 1 | January 12th, 2005 05:55 PM |
How do I get 3 series in sync with the x-axis? | zizbird | Charts and Charting | 10 | October 25th, 2004 01:23 PM |
SQL view of messed up action queries | Kendra | Running & Setting Up Queries | 2 | August 31st, 2004 09:53 PM |
Excel Combo Box using External Excel data... | Enigma007x | Worksheet Functions | 0 | August 4th, 2004 08:33 PM |