(I'm using Office 365 here in March of 2021. In his answer above, Philipp said: 'The problem is that a link to a closed file works with index( but not with index(indirect(' Does so 'dynamically' (though not exactly - you'll see).Updates data from a closed Excel file, and.
I want to clarify and second the solution posted by Philipp. Then the certain cell value of this worksheet will be referenced immediately. =INDEX('E:\Excel file\sheetname'!A:A,2,1)Īfter applying this formula, you will get a Select Sheet dialog box, please select a worksheet and then click the OK button. If you want to manually select a worksheet to reference, please use this formula In the formula, E:\Excel file\ is the full file path of the unopened workbook, test.xlsx is the name of the workbook, Sheet2 is the sheet name which contains the cell value you need to reference from, and A:A,2,1 means the cell A2 will be referenced in the closed workbook.
I have tried it on my current project (referencing a single cell and an array of cells) and it works well with no errors. Here is the link to the solution (correct formula) below. I too was looking for the answer to referencing cells in a closed workbook.
