Excel refer to cells in différent  closed worksbooks

Copper Contributor

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

@philippe_desrochers 

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?

@Hans Vogelaar 

 

Yes do you have a vba code to suggest? Im not verry good in VBA....

 

Thank You! 

@philippe_desrochers 

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
Hi!

I'm not able to make it work! Here my real data:

In the current workbooks:
Name of the closed file to extract data : in cell A7
Formula in cell D7
Here the formula:

= 'https://mapaq-my.sharepoint.com/personal/philippe_desrochers_mapaq_gouv_qc_ca/Documents/Bureau/TEST/[" & Range("A7").Value & "]NOM ENTREPRISE'!A1

I know the files path is weird, but it's explain by our cloud ..

the folder name is TEST
name of the sheet: = NOM ENTREPRISE

Here the VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A7"), Target) Is Nothing Then
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
' B7 is the cell with the formula
Range("D7").Formula = "https://mapaq-my.sharepoint.com/personal/philippe_desrochers_mapaq_gouv_qc_ca/Documents/Bureau/TEST/[" & Range("A7").Value & "]NOM ENTREPRISE'!A1"
' You can add lines like the above for other cells if required
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End If
End Sub


The VBA code do not seem to run like a macro, because he has no name. When i lauch macro, this vba code do not appear like a macro..

Thank you for you help!!!

@philippe_desrochers 

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.