In this article, I’ll show you three excellent methods on how to excel reference another workbook without opening in Excel. Furthermore, these strategies will allow you to solve the problem that brought you here. However, you will learn some important Excel features and functions in this post that will come in handy for any Excel task.
📁 Download Excel File
The sample worksheet that was used during the discussion is available to download for free right here.
Learn to Use Reference from Another Workbook Without Opening by These 3 Useful Techniques
Here in this article, we will learn how to excel reference another workbook without opening in Excel using different approaches. To be exact, I’ve provided a total of 5 methods down below.
To accomplish that we will a dataset of a grocery store price list of grocery items. We will try to create a new price list in a new workbook referencing another workbook without opening it.
1. Applying Paste Link Feature
We will apply the technique Paste Link feature to reference the excel file without opening it. The steps are described one by one below.
⬇️⬇️ STEPS ⬇️⬇️
- Go to the file from where you want to bring data.
- Copy the cells containing data.
- Then go to the main Excel file.
- Click the right button of the mouse and choose the Paste Link option and drag the fill handle.
- Then you will see the main workbook is linked to an old workbook like this.
- Then close the Excel sheet. And the link path will look like this.
That’s how you can reference the Excel file without opening it.
2. Using Cell Reference Directly
Now, we will apply Cell Reference directly to reference the Excel file without opening it. We demonstrated two different examples below.
2.1 From Closed Workbook in Desktop Folder
First and foremost, we will use the internal storage of our computer. The steps are explained one by one below.
⬇️⬇️ STEPS ⬇️⬇️
- Go to cell C6 of the main worksheet.
- Write down the following formula:
=‘H:\Softeko Articles\Article 10\[old.xlsx]Data’!C6
- Then press Enter and drag the fill handle to see the results.
Thus you can reference the Excel file without opening it.
2.2 From Closed Workbook in Cloud-Based Storage
Now we will use cloud storage. The steps are these.
⬇️⬇️ STEPS ⬇️⬇️
- Select cell C6 of the main worksheet.
- Write down the following formula:
=’https://d.docs.live.net/027ed76c4fecf138/[old 2.xlsx]Data’!C6
- Later press Enter and drag the fill handle to see the results.
Finally, you can reference the Excel file without opening it.
3. Utilizing Defined Name Feature
We will utilize the technique Defined Name feature to reference the Excel file without opening it. The processes are described one by one below.
⬇️⬇️ STEPS ⬇️⬇️
- Open the file from where you want to bring data.
- Copy the data you wish to make reference.
- Select Defined Names from the Formula tab.
- Then click OK.
- Consequently, open the main worksheet.
- Write down the following formula in cell C6.
=‘H:\Softeko Articles\Article 10\[old.xlsx]Data’!goods
- And last and not least hit Enter button and see the results.
Hence you can reference the Excel file without opening it.
4. Using INDEX Function
We will apply the method INDEX function to reference the Excel file without opening it. The processes are described in the sequence below.
⬇️⬇️ STEPS ⬇️⬇️
- Open the main worksheet.
- Select cell C6.
- Write down the following formula:
=INDEX(‘H:\Softeko Articles\Article 10\[old.xlsx]Data’!C:C,6,1)
- Press Enter and drag the fill handle.
That’s how you can reference the Excel file without opening it.
5. Using VBA Macro to Reference Another Workbook Without Opening
Finally, We will apply the technique VBA macro feature to reference the excel file without opening. The steps are described one by one below.
⬇️⬇️ STEPS ⬇️⬇️
- Go to Visual Basics from the Developer tab.
- Select Insert> module option.
- Write down the following VBA code:
Sub linkdata()
Application.ScreenUpdating = False
Set Source_workbook = Workbooks.Open("H:\Softeko Articles\Article 10\old.xlsx")
Workbooks("old").Worksheets("Data").Range("C6:C9").Copy _
Workbooks("new").Worksheets("5").Range("C6:C9")
Source_workbook.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub
- Press F5 to run the code and get the results.
Accordingly, you can reference the Excel file without opening it.
📝 Takeaways from This Article
📌
You can reference the Excel file without opening using the Paste Link feature.
📌
Moreover, you can reference the Excel file without opening the Defined Name feature.
📌
In addition, you can reference the Excel file without opening the INDEX Function.
📌
Finally, you can reference the Excel file without opening VBA macros.
📄 Important Notes
🖊️
Verify that you’ve employed Absolute Reference when it was required. To get an absolute cell reference, press F4.
🖊️
Always look for ways that are easy to use in different situations.
🖊️
Every time you try to use shortcuts on the keyboard.
Conclusion
First and foremost, I hope you were able to apply the principles I taught in this Excel lesson on how to excel reference another workbook without opening. As you can see, there are numerous ways to accomplish this. However, choose the strategy that is best suited to your situation with care. Most essential, if you become confused in any of the processes, I recommend repeating them. After reviewing the Excel file in the practice workbook that I’ve shared above, practice on your own since practice makes perfect. Above all, I sincerely hope you can put it to good use. Please share your thoughts in the comments section about how you felt throughout the article and what we should change or add to make things better for you. However, if you have any Excel-related issues, please leave them in the comments section. The Excelden team is available to answer your inquiries at any time. Finally, keep reading to educate yourself. Please visit our website Excelden.com for further Excel-related articles.