[Solved]Copy data from External Files Referenced in column A

I am using Windows 7 with Excel 2007. I am new at this and would appreciate any help given.

My master workbook has in column A filenames (over 100) for each row of data (ie C:\Users\David\Desktop\Experimental\2-a\2-A HAY PRODUCTION-dg2.xlsm) I need a formula that will open each file listed in column A and copy the contents of cell $D$2 to the active worksheet cell B2 etc.

try this...

='[C:\Users\David\Desktop\Experimental\2-a\2-A HAY PRODUCTION-dg2.xlsm]sheet1'!$D$2

if that doesnt work, just open the other file, reference it from your master and "enter". that will give you the correct formula for that reference, and after that, provided the cell reference is the same in every file, i will help you set up an indirect() formula to reference the rest of the files. if the cell reference is not the same, we can work through that as we get there

It did not work.. so i did as you instructed and this is the formula "[ code ]" ='[2-A KLEINGRASS HAY PRODUCTION-dg2.xlsm]2-A'!$D$2"[/code]"
the problem is that column A has the complete path and file name listed. Each row may have a different file name.

thats fine, i just needed to see the path etc

now you need to apply the indirect() function to reference that.

in column B, copy this....=INDIRECT("'"&A1) or whatever your reference is. this should give you the value you want

and btw, to use the "code" function in here, remove the spaces from the [ code ], i had to add that else it would have actually PUT what i said in the code box

let me know how this works for you?

2-a.zip In the attached zip file, I have the master workbook and then the files in a sub folder. I editied the files so as not to include any confidential information. You will need to change the path for column A to match the path on your computer.

After I input the formula and the cell references I get an error with the 2-A (sheet name). which I am not sure how to fix. I put an ' before the formula so I could save it with the error. I am having a hard time figuring this out and I really appreciate your assistance. Use the zip file since it contains the files and sub-folders.

to begin with, you dont have the sheet name and cell reference in your reference. also, im not completely sure that indirect will work on closed workbooks anyway (im checking on that)

Look at the 2-a.zip files in the link at the beginning of the paragraph. Would I be able to use the Indirect.ext function?

ok i have checked and No, INDIRECT() does not work on a closed workbook. There is a function called INDIRECT.EXT that is available as part of a free download called MOREFUNC. It does work on a closed workbook.

Here's a link: http://download.cnet.com/Morefunc/30...-10423159.html

(copied from a responce from Cutter)

I already downloaded this but I can't get it to work right. I must be typing something wrong in the syntax? I get an error when I try this link.

Even with the 2nd workbook open if I type this code =INDIRECT('["&A8&"]2-A'!$D$2) I get a #REF! error. The A8 represents the cell where the filename with path is stored (Please look at the zip file above). 2-A is the name of the worksheet which is the same for all files. $D$2 is the cell address of the needed data.

Your help is greatly appreciated.

Alright here's what I have accomplished, I removed the path from the files, put the worksheet ID and Cell address in a separate cell and created the following formula - =INDIRECT.EXT("'["&A9&"]"&$C$5&"'!"&$C$1).

• A9 is the filename trying to access,
• C5 is the worksheet name same for all files
• C1 the cell address same for all files.

This worked when the files are open but when I close the files the formula results changed to #REF!

I had to put the path into the formula in order to be able to do the calculations with all workbooks closed.

Thanks for all of your help.