Creating workbook references based on a list of filenames

%3CLINGO-SUB%20id%3D%22lingo-sub-3360627%22%20slang%3D%22en-US%22%3ECreating%20workbook%20references%20based%20on%20a%20list%20of%20filenames%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3360627%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20list%20of%20files%20from%20which%20I%20would%20like%20to%20extract%20data%20-%20data%20is%20in%20the%20same%20location%20in%20each%20of%20these%20files.%3C%2FP%3E%3CP%3EI%20want%20to%20write%20a%20formula%20(and%20I%20know%20that%20INDIRECT%20only%20works%20with%20open%20workbooks%2C%20so%20that%20wouldn't%20work)%20that%20references%20those%20filenames%20and%20returns%20the%20value%20in%20the%20referenced%20or%20linked%20workbook.%3C%2FP%3E%3CP%3EI%20know%20that%20I%20can%20type%20'%3D'%20and%20then%20click%20over%20to%20the%20workbook%20in%20question%2C%20but%20I%20have%20lots%20of%20them%20to%20do%2C%20and%20am%20looking%20for%20a%20better%20way.%3C%2FP%3E%3CP%3EAny%20suggestions%20would%20be%20helpful.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3360627%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3363050%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20workbook%20references%20based%20on%20a%20list%20of%20filenames%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3363050%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1387513%22%20target%3D%22_blank%22%3E%40BobBobWalker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20said%20with%20formula%20the%20file%20should%20be%20open.%3CBR%20%2F%3EHere%20is%20a%20small%20formula%20example%3A%3C%2FP%3E%3CP%3E%3DSUMPRODUCT('C%3A%5CDocuments%20and%20Settings%5COwner%5COwn%20Documents%5CWorkFiles%5CCustomer%5CEvaluations%5C%5B%22%26amp%3B%24A%241%26amp%3BG%243%26amp%3B%22.xls%5DNewCustom_Active'!AC1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20you%20could%20use%20VBA%20to%20read%20data%20from%20a%20closed%20workbook.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F2989-excel-import-data-from-closed-workbook.html%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EHow%20To%20Import%20%2F%20Copy%20Data%20From%20Closed%20Workbook%20Into%20Current%20Workbook%3F%3C%2FA%3E%3C%2FP%3E%3CP%3EStandard%20disclaimer%3A%20none%20Microsoft%20site.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20may%20also%20recommend%2C%20always%20add%20your%20Excel%20version%2C%20operating%20system%2C%20storage%20medium%20and%20other%20necessary%20information%20on%20your%20topic%20as%20information.%3C%2FP%3E%3CP%3EThis%20way%20you%20will%20get%20a%20solution%20proposal%20much%20faster%20and%20more%20accurately.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20I%20could%20help%20you%20with%20these%20information%20%2F%20links.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel-blog%2Fmeet-niko-chatzoudis-excel-forum-contributor%2Fba-p%2F2941385%22%20target%3D%22_blank%22%3ENikolinoDE%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have a list of files from which I would like to extract data - data is in the same location in each of these files.

I want to write a formula (and I know that INDIRECT only works with open workbooks, so that wouldn't work) that references those filenames and returns the value in the referenced or linked workbook.

I know that I can type '=' and then click over to the workbook in question, but I have lots of them to do, and am looking for a better way.

Any suggestions would be helpful.

1 Reply

@BobBobWalker 

 

As you said with formula the file should be open.
Here is a small formula example:

=SUMPRODUCT('C:\Documents and Settings\Owner\Own Documents\WorkFiles\Customer\Evaluations\["&$A$1&G$3&".xls]NewCustom_Active'!AC1)

 

But you could use VBA to read data from a closed workbook.

How To Import / Copy Data From Closed Workbook Into Current Workbook?

Standard disclaimer: none Microsoft site.

 

 

 

If I may also recommend, always add your Excel version, operating system, storage medium and other necessary information on your topic as information.

This way you will get a solution proposal much faster and more accurately.

 

Hope I could help you with these information / links.

 

NikolinoDE

I know I don't know anything (Socrates)