Referencing an external worksheet in an indirection through a separate tab

%3CLINGO-SUB%20id%3D%22lingo-sub-1525163%22%20slang%3D%22en-US%22%3EReferencing%20an%20external%20worksheet%20in%20an%20indirection%20through%20a%20separate%20tab%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1525163%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20workbook%20with%20a%20worksheet%20(tab)%20named%20West%2C%20and%20a%20worksheet%20named%20DBname.%20%26nbsp%3BCell%20A1%20of%20worksheet%20DBname%20contains%20the%20name%20of%20another%20workbook%20named%20Foobar.xlsx.%20%26nbsp%3BIn%20cell%20I4%20of%20the%20West%20worksheet%20I%20want%20to%20use%20the%20contents%20of%20DBname!A1%20to%20identify%20that%20external%20workbook%2C%20and%20resolve%20the%20path%20to%20a%20tab%20named%20'DatabaseName'%20and%20cell%20A1%20in%20that%20workbook.%20%26nbsp%3BCell%20A1%20in%20tab%20DatabaseName%20in%20Foobar.xlsx%20contains%20%22This%20is%20the%20database%22.%20%26nbsp%3B%26nbsp%3BDBname!A1%20contains%20%22Foobar.xlsx'.%20%26nbsp%3BIn%20summary%2C%20West!I4%20wants%20to%20use%20the%20contents%20of%20cell%20A1%20in%20the%20DBname%20tab%20as%20the%20name%20of%20another%20workbook%2C%20and%20wants%20to%20pull%20the%20contents%20of%20cell%20A1%20in%20the%20tab%20DatabaseName%20and%20display%20that%20as%20the%20resolved%20value%20of%20cell%20I4.%3C%2FP%3E%3CP%3ESo%2C%20West!I4%20needs%20to%20contain%20something%20like%20%22%3D%5B'DBname'!A1%5DDatabaseName!%24A%241%22.%20%26nbsp%3BBut%20Excel%20reports%20that%20%22%5B'DBname'%20is%20not%20valid%22.%20%26nbsp%3B%20By%20the%20way%2C%20this%20is%20in%20Excel%202011%20on%20Mac.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1525163%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-1527167%22%20slang%3D%22en-US%22%3ERe%3A%20Referencing%20an%20external%20worksheet%20in%20an%20indirection%20through%20a%20separate%20tab%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1527167%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F730071%22%20target%3D%22_blank%22%3E%40DFClark%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20refer%20to%20the%20following%3A%3C%2FP%3E%3CP%3E%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%22%3Ehttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F28461672%2Freferencing-value-in-a-closed-excel-workbook-using-indirect%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have a workbook with a worksheet (tab) named West, and a worksheet named DBname.  Cell A1 of worksheet DBname contains the name of another workbook named Foobar.xlsx.  In cell I4 of the West worksheet I want to use the contents of DBname!A1 to identify that external workbook, and resolve the path to a tab named 'DatabaseName' and cell A1 in that workbook.  Cell A1 in tab DatabaseName in Foobar.xlsx contains "This is the database".   DBname!A1 contains "Foobar.xlsx'.  In summary, West!I4 wants to use the contents of cell A1 in the DBname tab as the name of another workbook, and wants to pull the contents of cell A1 in the tab DatabaseName and display that as the resolved value of cell I4.

So, West!I4 needs to contain something like "=['DBname'!A1]DatabaseName!$A$1".  But Excel reports that "['DBname' is not valid".   By the way, this is in Excel 2011 on Mac.

 

1 Reply