Formula referencing from other workbooks

Question

I'm quite new to VBA and I'm having trouble debugging a certain code. What I want is a cell formula that has the formula link to another spreadsheet. However, I want to add cells from multiple workbooks. For example, if we had workbook1 and workbook 2. I want in cell F10 in final workbook to have formula reading '[workbook1]Sheet1'!!F10' + '[workbook2]Sheet1'!!F10'

I like to make the formula as flexible and have the following conditions

I like to have an open directory that lets me select excel files that I want as part of the formula
I can add as many external spreadsheets as possible
The final spreadsheet initially will have zeroes in them. I want to replace this with a formula link.

How i decided to code this is by first replacing the zero cell of the final workbook with cell F10 of first excel file selected from a directory. Once this step is done, any additional workbooks selected from directory will add on as an extra formula link to the cell. Below is a code I attempted but I can not figure why it doesn't work. Could anyone please let me know what is going wrong? Thanks.

Sub Sum_workbooks_Form()
    Dim FileNameXls, f
    Dim wb As Workbook, i As Integer

    FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", MultiSelect:=True)

    If Not IsArray(FileNameXls) Then Exit Sub

    
    
    For Each f In FileNameXls
        
       
        
        Set wb = Workbooks.Open(f)
            
           
               If ThisWorkbook.Sheets("Sheet1").Cells(11, 6).Value = 0 Then
                    ThisWorkbook.Sheets("Sheet1").Cells(11, 6).Formula = "=[" & wb.Name & "]Sheet1!" & Cell(11, 6).Name
                Else
                    ThisWorkbook.Sheets("Sheet1").Cells(11, 6).Formula = "=[" & ThisWorkbook.Name & "]Sheet1!" & Cell(11, 6).Name & " + [" & wb.Name & "]Sheet1!" & Cell(11, 6).Name
                End If
          

            
            
        wb.Close SaveChanges:=False
    Next f

   
End Sub

Corrado Barbero Corrado Barbero · Accepted Answer · 2020-07-06T03:08:06

Well, normally a reference to cell F10 of Sheet1 of Book2 is expressed like this in a formula:

=[Book2]Sheet1!$F$10

Is it possible for you to reference the Sheet NAME instead of the NUMBER? If yes, the first of your assignments should look like this:

ThisWorkbook.Sheets(9).Cells(11, 6).Formula = "=[" & wb.Name & "]Sheet1!$F$10"

Where Sheet1 is of course the name of your Sheets(9)

Formula referencing from other workbooks

1 Answers