Sep 14 2022 11:18 AM
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!
Sep 14 2022 01:54 PM
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?
Sep 15 2022 06:39 AM
Sep 15 2022 12:44 PM
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
Sep 21 2022 11:51 AM
Sep 21 2022 01:26 PM
This is not a macro to be run by the user (you). It is a so-called event procedure that should be run automatically by Excel when you change the value of cell A7.
Make sure that the code is in the worksheet module, i.e. the module that is activated by right-clicking the sheet tab in Excel and selecting 'View Code' from the context menu.