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.
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.
- First, open the worksheet that will remain close named Closed.xlsm.
- Then copy the required cells.
- Now, we copy the range B5 to C9.
- Then, open the other workbook.
- Go to Cell B5.
- Click the right button on the mouse.
- Select Paste Link (N).
- Now, we can see that data is pasted into our desired cells.
- Now, we see the reference code of Cell C9. That is:
- Now, close the Closed.xlsm worksheet.
- And at that time reference will also change accordingly. That is:
This is how we can reference one worksheet and then close that worksheet.
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.
- 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:
- Then press Enter.
- Now, pull the Fill Handle icon to the last cell.
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.
- Now, we apply the modified formula in Cell B5.
- The formula is:
- 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.
- We will enter the below code on Cell B5.
- Then press Enter.
- Now, the available sheets of file Closed.xlsm is showing.
- Choose the desired sheet.
- Now, press OK.
Those are some processes that we can refer to any workbook without opening that file.
- How to Link Multiple Cells from Another Worksheet in Excel (5 Easy Ways)
- Link Excel Sheets to Another Sheet (5 Ways)
- How to Link Files in Excel (5 Different Approaches)
- Link Word Document to Excel (2 Easy Methods)
- How to Link Cell to Another Sheet in Excel (7 Methods)
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.
- This Sample.xlsm worksheet is saved in One drive. We will refer this workbook to another workbook.
- Copy the Range B5:C9.
- Now, go to the destination workbook.
- In Cell B5, click the right button on the mouse.
- Then click the Paste Link(N).
Copied data is pasted into the selected cells.
- Now, close the Sample.xlsm workbook located on One drive.
- Now, get the reference of Cell C9 and that is:
Here, we referred to a workbook saved on the cloud.
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.
- 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.
- Now, we give a name and select the cell range.
- Then press OK.
- Now, close the source file and enter the destination file.
- Go to Cell B5 and manually input the below code:
- Then press Enter.
Here, we get data from the closed workbook using the defined name reference.
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.
- Open the destination file.
- Go to the Developer tab.
- Then choose Record Macro.
- Named the macro as Referencedata.
- Then press OK.
- 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
- 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.
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.
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.
- How to Link Data from One Spreadsheet to Another in Excel
- Link Sheets to a Master Sheet in Excel (5 Ways)
- How to Link Two Sheets in Excel (3 Ways)
- Link Data in Excel from One Sheet to Another (4 Ways)
- How to Link Sheets in Excel with a Formula (4 Methods)
- Transfer Specific Data from One Worksheet to Another for Reports