3 Tricks to Reference Another Workbook Without Opening in Excel

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.

Dataset for excel reference another workbook without opening

Method

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.

Copying 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.

Selecting Paste Link option

  • Then you will see the main workbook is linked to an old workbook like this.

cell reference while source workbook open

  • Then close the Excel sheet. And the link path will look like this.

cell reference while source workbook open

That’s how you can reference the Excel file without opening it.

Method

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.

Cell Reference

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

Inserting formula

  • Then press Enter and drag the fill handle to see the results.

Final result

Thus you can reference the Excel file without opening it.

Cell Reference

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

Inserting formula

  • Later press Enter and drag the fill handle to see the results.

Final output

Finally, you can reference the Excel file without opening it.

Method

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.

Selecting define name feature

  • Then click OK.

selecting defined range

  • 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.

Inseringformula as defined

Hence you can reference the Excel file without opening it.

Method

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)

Inserting formula using INDEX function

  • Press Enter and drag the fill handle.

Final outcome

That’s how you can reference the Excel file without opening it.

Method

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.

Choosing Visual Basic wizard

  • Select Insert> module option.

Selecting module fromr insert 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

Inserting VBA code

  • Press F5 to run the code and get the results.

Final result

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.

(Visited 33 times, 1 visits today)
Md Shamse Tabrej

Md Shamse Tabrej

Hi, I'm Shamse Tabrej. I'm an admirer of Excel who enjoys sharing and solving problems in Excel. I make an effort to investigate all of the choices so that I may point readers toward the most viable solutions. Both the globe and its problems are boundless. Let's assist one another in developing. Reader questions and suggestions are most welcome. 

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo