Dynamic link to a closed workbook with multiple sheets

[agalarra]

Hi all,

I have the following formula in cell A2 of my open workbook:

='[ClosedWorkbook.xlsx]Sheet1'!A2

In cell B2 of the open workbook, I have the same link but referred to cell B2 of a closed workbook (i.e. ='[ClosedWorkbook.xlsx]Sheet1'!B2). This repeats until cell H2. Please note that the closed workbook contains 10 worksheets (Sheet1 to Sheet10).

I would like to be able to dynamically change this external link to a different sheet without opening the linked workbook. For example, if the value of cell A1 is changed from "Sheet1" to "Sheet2", then the link (and values) will change to ='[ClosedWorkbook.xlsx]Sheet2'!

Is there a way of doing this and update values without opening the linked worksheet?

Thanks in advance