Reference from Another Excel Workbook without Opening (5 Examples)

Microsoft Excel has a wide range of applications for our day-to-day use. One of them is to refer to any cell or range. We can refer to cells or ranges in the same sheet or other sheets, or another workbook. When we refer from one workbook to another, we should be very careful. But referring to one workbook without opening that to another workbook is very tricky. We have to ensure some vital information of that closed file. So, this article is all about using the reference from another workbook without opening it in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Source File:

Destination File:


5 Methods of Using Reference from Another Workbook Without Opening It

Here, we will discuss some methods to reference any workbook without opening that file in Excel.


1. Reference from Another Workbook Using the Paste Link Option

Here, we will show how to reference another workbook using the paste link.

Step 1:

  • First, open the worksheet that will remain close named Closed.xlsm.
  • Then copy the required cells.
  • Now, we copy the range B5 to C9.

Step 2:

  • Then, open the other workbook.
  • Go to Cell B5.
  • Click the right button on the mouse.
  • Select Paste Link (N).

Reference from Another Workbook Using the Paste Link Option

Step 3:

  • Now, we can see that data is pasted into our desired cells.

Step 4:

  • Now, we see the reference code of Cell C9. That is:
=[Closed.xlsm]Sheet1'!C9

Reference from Another Workbook Using the Paste Link Option

Step 5:

  • Now, close the Closed.xlsm worksheet.
  • And at that time reference will also change accordingly. That is:
='C:\Users\Alok\Desktop\25-0056-1688\[Closed.xlsm]Sheet1'!C9

Reference from Another Workbook Using the Paste Link Option

This is how we can reference one worksheet and then close that worksheet.

Read More: Link Excel Workbooks for Automatic Update (5 Methods)


2. Reference from a Closed Excel Workbook in Desktop Folder

Here, we will show if the reference workbook is saved on the local computer.

We will refer Closed.xlsm file on Open.xlsm without opening the Closed.xlsm file.

Here, we need to manually input the reference.

Step 1:

  • First, open the Open.xlsm file.
  • Now, go to Cell B5.
  • We need to input the file path, workbook name, sheet name, and cell reference here.
  • Here, we input the below formula:
='C:\Users\Alok\Desktop\25-0056-1688\[Closed.xlsm]Sheet1'!B5

Reference from a Closed Excel Workbook in Desktop Folder

Step 2:

  • Then press Enter.

Step 3:

  • Now, pull the Fill Handle icon to the last cell.

Reference from a Closed Excel Workbook in Desktop Folder

We can see that the rest of the cells are filled with data from the closed worksheet.

We can also do this by using a range in the formula.

Step 4:

  • Now, we apply the modified formula in Cell B5.
  • The formula is:
='C:\Users\Alok\Desktop\25-0056-1688\[Closed.xlsm]Sheet1'!B5:C9

Reference from a Closed Excel Workbook in Desktop Folder

Step 5:

  • Again, press Enter.

In this way, we can enter a whole of data without opening the worksheet.

In one more situation, we may forget the sheet name when entering the formula. For that purpose, we have a solution.

Step 6:

  • We will enter the below code on Cell B5.
='C:\Users\Alok\Desktop\25-0056-1688\[Closed.xlsm]SheetName'!B5:C9

Reference from a Closed Excel Workbook in Desktop Folder

Step 7:

  • Then press Enter.
  • Now, the available sheets of file Closed.xlsm is showing.
  • Choose the desired sheet.

Reference from a Closed Excel Workbook in Desktop Folder

Step 8:

  • Now, press OK.

Those are some processes that we can refer to any workbook without opening that file.

Read More: How to Link Excel Workbooks (4 Effective Methods)


Related Readings


3. Reference from a Closed Excel Workbook from the Cloud

We may need to refer to any workbook file from the cloud in another workbook. In this section, we will discuss this topic. When we refer to any file from the local computer there is a problem that is if we change the location of the file the reference will not work. But when we add any reference from the cloud workbooks this problem will not occur.

Step 1:

  • This Sample.xlsm worksheet is saved in One drive. We will refer this workbook to another workbook.
  • Copy the Range B5:C9.

Reference from a Closed Excel Workbook from the Cloud

Step 2:

  • Now, go to the destination workbook.
  • In Cell B5, click the right button on the mouse.

Step 3:

  • Then click the Paste Link(N).

Reference from a Closed Excel Workbook from the Cloud

Copied data is pasted into the selected cells.

Step 4:

  • Now, close the Sample.xlsm workbook located on One drive.
  • Now, get the reference of Cell C9 and that is:
='https://d.docs.live.net/03e01967881debf5/Softeko/25-0056-1688/[Sample.xlsm]Sheet'!C9

Reference from a Closed Excel Workbook from the Cloud

Here, we referred to a workbook saved on the cloud.

Read More: How to Link Two Workbooks in Excel (5 Methods)


4. Use Defined Name for Reference from Another Workbook

In this section, we will show how to refer to any workbook using the defined name.

Step 1:

  • First, define the name of the source data.
  • Go to the Formulas tab.
  • Then choose the Define Name option from the Define Name drop-down.

Use Defined Name for Reference from Another Workbook

Step 2:

  • Now, we give a name and select the cell range.
  • Then press OK.

Use Defined Name for Reference from Another Workbook

Step 3:

  • Now, close the source file and enter the destination file.
  • Go to Cell B5 and manually input the below code:
='C:\Users\Alok\Desktop\25-0056-1688\[Closed.xlsm]Sheet1'!Fruit

Use Defined Name for Reference from Another Workbook

Step 4:

  • Then press Enter.

Here, we get data from the closed workbook using the defined name reference.

Read More: How to Reference Worksheet Name in Formula in Excel (3 Easy Ways)


5. Apply VBA Macro to Refer to a Workbook Without Opening It

We can also use VBA macro to refer to any workbook without opening that file.

Step 1:

  • Open the destination file.
  • Go to the Developer tab.
  • Then choose Record Macro.
  • Named the macro as Referencedata.
  • Then press OK.

Apply VBA Macro to Refer to a Workbook Without Opening It

Step 2:

  • Write the below code on the command 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

Apply VBA Macro to Refer to a Workbook Without Opening It

Step 3:

  • Then press F5 to run the code.

Here, one thing needs to mention only data will be imported from the referred workbook, the format will not be copied.

Read More: How to reference cell in another Excel sheet based on cell value!


Problems with Referencing Multiple Excel Workbooks

Here we will discuss some problems with referencing workbooks.

1. Referred Data Location may Change

When a closed workbook changes its location, the referred workbook does not have any idea of the modified location. After that, if any changes are placed in the source workbook they will not reflect on the destination file.

2. Sub-links not Updated Promptly

If multiple workbooks are referred to each other then the situation becomes complicated. Like workbook 1 is referred to as workbook 2; Workbook 2 is referred to as workbook 3. Then the update of workbook 1 will not reflect on workbook 3 properly.

3. Data from Previous Version is Retrieved

Data can be retrieved only from the last saved version of a file. If you change the source file data but do not save that data will not be shown on the destination. After saving the file the changes will be shown.


Conclusion

In this article, we how to reference from another workbook without opening it in Excel. I hope this will satisfy your needs. Please have a look at our website ExcelDemy.com and give your suggestions in the comment box.


Further Readings

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo