New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Retrieve Data From Another Workbook

Free Excel Tips and Tutorials

Retrieve Data From Another Workbook

Postby Sisyphus » Thu Mar 08, 2012 7:38 pm

You can access the data in any cell in any worksheet and import its value into your active worksheet. This is the basic formula to do it with:
Code: Select all
='D:\My Documents\PathName\[WorkbookName.xls]Worksheet Name'!$B$4

This formula consists of not less than five parts: Drive, Path, Workbook, Worksheet and Cell.
Follow these steps for easily creating it:
1. Open the 'Source' workbook from which you wish to import data.
2. In the 'Target' workbook type an equal sign in the cell where you want the data to appear.
3. Switch to the 'Source' workbook and click on the cell whose data you want to import.
4. Enter
Excel will now have placed a part of the above formula in the 'Target' cell, like this:
Code: Select all
[WorkbookName.xls]Worksheet Name'!$B$4
. It will also have created a Link which you can look at and modify from Edit / Links. There you will see the Status mentioned as "Unknown", but if you click the 'Check Status' button this information changes to 'Open' because the 'Source' workbook is open.
5. Now close the 'Source' workbook.
Excel will automatically add the drive and path to the formula in each linked cell. Be sure to save this change in your workbook, because if you don't Excel will not know how to find the Link on next use unless the other workbook is open.

In the Edit / Links dialog box you can set how you want the Link to behave. You can make the Link update automatically whenever you open the workbook. Or you can press the 'Break Link' button to replace the linking formula with the imported data. You can open the linked 'Source' workbook from here, change its location or update the imported values at any time.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Tips and Tutorials

Who is online

Users browsing this forum: No registered users and 16 guests