How to Use Reference from Another Excel Workbook Without Opening it – 5 Examples

Example 1 – Use a Reference from Another Workbook Using the Paste Link Option

Step 1:

• Open the worksheet: Closed.xlsm.
• Copy B5:C9.

Step 2:

• Open the other workbook.
• Go to B5.
• Right-click.
• Select Paste Link (N).

Step 3:

• Data is pasted.

Step 4:

• The reference code of C9 is:

Step 5:

• Close the Closed.xlsm worksheet.
• The time reference will also change:

Read More: Link Excel Workbooks for Automatic Update

Example 2 – Use a Reference from a Closed Excel Workbook in a Desktop Folder

Refer Closed.xlsm in Open.xlsm without opening the Closed.xlsm file.

Step 1:

• Open the Open.xlsm file.
• Go to B5.
• Enter the file path: workbook name, sheet name, and cell reference.
• Enter the formula:

Step 2:

• Press Enter.

Step 3:

• Drag down the Fill Handle to see the result in the rest of the cells.

Step 4:

• Use the modified formula in B5:

Step 5:

• Press Enter.

The whole data range was referred without opening the worksheet.

Step 6:

• Enter the code in B5.

Step 7:

• Press Enter.
• The available sheets of Closed.xlsm are displayed.
• Choose a sheet.

Step 8:

• Click OK.

Example 3 – Reference a Closed Excel Workbook from the Cloud

Step 1:

• Sample.xlsm worksheet is saved in One drive. Refer this workbook to another workbook.
• Copy B5:C9.

Step 2:

• Go to the destination workbook.
• In B5, right-click.

Step 3:

• Click Paste Link(N).

Copied data is pasted.

Step 4:

• Close the Sample.xlsm workbook located in One drive.
• Get the reference of C9:

Read More: How to Link Two Workbooks in Excel

Example 4 – Use a Defined Name to Refer to Another Workbook

Step 1:

• Define the name of the source data.
• Go to Formulas.
• Choose Define Name in the Define Name drop-down.

Step 2:

• Name  the data and select the cell range.
• Click OK.

Step 3:

• Close the source file and go to the destination file.
• In B5, enter the code:

Step 4:

• Press Enter.

Example 5 – Apply a VBA Macro to Refer to a Workbook Without Opening It

Step 1:

• Open the destination file.
• Go to the Developer tab.
• Choose Record Macro.
• Name the macro as Referencedata.
• Click OK.

Step 2:

• Enter the code in the module.

Sub Importdata1()
Dim AreaAddress As String
Sheet1.UsedRange.Clear
Sheet1.Cells(1, 1) = "= 'C:\Users\Alok\Desktop\25-0056-1688\" & "[Closed.xlsm]Sheet2'!RC"
AreaAddress = Sheet1.Cells(1, 1)
With Sheet1.Range(AreaAddress)
.FormulaR1C1 = "=IF('C:\Users\Alok\Desktop\25-0056-1688\" & "[Closed.xlsm]Sheet1'!RC="""",NA(),'C:\Users\Alok\Desktop\25-0056-1688\" & _
"[Closed.xlsm]Sheet1'!RC)"
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
On Error GoTo 0
.Value = .Value
End With
End Sub

Step 3:

• Press F5 to run the code.

Only data will be imported from the referred workbook, the format will not be copied.

Problems with Referencing Multiple Excel Workbooks

1. Referred Data Location may Change

When a closed workbook changes its location, the referred workbook loses reference.

2. Sub-links not Updated Promptly

Do not refer multiple workbooks to each other to avoid incorrect updates.

3. Data from Previous Versions is not Retrieved

Data can only be retrieved from the last saved version of a file.

Download Practice Workbook

Download the practice workbook.

Source File:

Destination File:

Related Articles

• [Fixed!] ‘This workbook contains links to other data sources’ Error in Excel
• [Fixed!] Excel Links Not Working Unless Source Workbook Is Open
• [Fixed!] This Workbook Contains Links to One or More External Sources That Could Be Unsafe

<< Go Back To Linking Workbooks in Excel | Linking in Excel | Learn Excel