Referencing a Closed Workbook using a cell value as the workbook name.

%3CLINGO-SUB%20id%3D%22lingo-sub-1492184%22%20slang%3D%22en-US%22%3EReferencing%20a%20Closed%20Workbook%20using%20a%20cell%20value%20as%20the%20workbook%20name.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1492184%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20reference%20specific%20cells%20in%20a%20bunch%20of%20closed%20workbooks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%26nbsp%3B%3D'C%3A%5CUsers%5CUserA%5CWorkbooks%5C%5BWorkbookA.xlsx%5DSheet1'!E13%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22%22%3ETyping%20the%20workbook%20names%20for%20every%20reference%20is%20clearly%20a%20waste%20of%20time.%20How%20can%20I%20call%20in%20the%20workbook%20name%20into%20the%20reference%20formula%20from%20a%20list%20of%20names%20that%20resides%20in%20the%20same%20sheet%20as%20the%20reference%20formula.%20In%20the%20example%20above%20my%20thinking%20is%20to%20replace%20WorkbookA%20with%20a%20cell%20reference%20like%20A2%20or%20A15%20from%20within%20the%20same%20sheet.%20All%20of%20my%20attempts%20have%20failed.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1492184%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1492315%22%20slang%3D%22en-US%22%3ERe%3A%20Referencing%20a%20Closed%20Workbook%20using%20a%20cell%20value%20as%20the%20workbook%20name.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1492315%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F711345%22%20target%3D%22_blank%22%3E%40NoviceExtraordinaire%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20didn't%20read%20carefully%20but%20perhaps%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fquestions%2F28461672%2Freferencing-value-in-a-closed-excel-workbook-using-indirect%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F28461672%2Freferencing-value-in-a-closed-excel-workbook-using-indirect%3C%2FA%3E%26nbsp%3Bis%20some%20workaround.%20Simply%20using%20INDIRECT()%20doesn't%20work%20with%20closed%20workbooks.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I need to reference specific cells in a bunch of closed workbooks.

 

Example: ='C:\Users\UserA\Workbooks\[WorkbookA.xlsx]Sheet1'!E13

 

Typing the workbook names for every reference is clearly a waste of time. How can I call in the workbook name into the reference formula from a list of names that resides in the same sheet as the reference formula. In the example above my thinking is to replace WorkbookA with a cell reference like A2 or A15 from within the same sheet. All of my attempts have failed. 

1 Reply

@NoviceExtraordinaire 

I didn't read carefully but perhaps here https://stackoverflow.com/questions/28461672/referencing-value-in-a-closed-excel-workbook-using-indi... is some workaround. Simply using INDIRECT() doesn't work with closed workbooks.