Forum Discussion
Excel refer to cells in différent closed worksbooks
Hello,
In workbooks 1. i have to refer to cells in différent closed worksbooks in a folder. ( Documents)
I used this formula: ='C:\Documents\[nameofthe files.xls ]Sheet2'! A1
In the same workbooks 1 , i want the nameofthe files come from a particular cell, by example, in the cell B3.
in workbook 1 cell B3, we have the nameofthefiles.xls
i am looking the way to write the instruction with the reference to cell B3 who contain the name
this way dont work:='C:\Documents\[=B3 ]Sheet2'! A1
Can you help me?
thank you!
5 Replies
If the other workbook is open, you could use the INDIRECT function, but unfortunately, that won't work with a closed workbook.
You might use VBA code to change the formula when you change the value of B3. Would that be acceptable?
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code listed below into the worksheet module, and modify it to suit your setup.
Switch back to Excel and save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("B3"), Target) Is Nothing Then On Error Resume Next Application.ScreenUpdating = False Application.DisplayAlerts = False ' A5 is the cell with the formula Range("A5").Formula = "='C:\Documents\[" & Range("B3").Value & "]Sheet2'!A1" ' You can add lines like the above for other cells if required Application.DisplayAlerts = True Application.ScreenUpdating = True End If End Sub
