Friday, May 27, 2011

Link Data from another Spreadsheets and Workbooks with Excel Formulas


In the first part of this article, we discussed referencing multiple sheets within a single file.
It is now time to tackle the linking of multiple workbooks together: referencing data from different files.

Budget is from Venus, Expenses are from Mars

In the previous sample we assumed that the budget and actual expenses are found on the same file. However, this is usually not the case, as different people fill up the contents of those tables and the budget is usually fixed at the start of a year as opposed to the expenses which are totaled each month at a time.
This sometimes results in those tables being in different files. But we still need to be able to create a difference sheet, and Excel is flexible enough to let us do so.
We’ve separated the Budget workbook from the Expenses workbook, and also created an empty Difference workbook in which we’ll put the difference between them.

Differing the Files

After opening all three Excel files, make sure you have the Difference workbook active, and let’s start:
·         Select cell B5
·         Type in = to start the formula
·         Using the mouse, activate the Budget workbook
·         Select cell B5; the Formula Bar should display: ='[XYZ Office Budget.xlsx]Budget'!$B$5
·         Type in - to deduct the actual expenses; the bar will display: ='[XYZ Office Budget.xlsx]Budget'!$B$5-
·         Using the mouse, activate the Actual Expenses workbook
·         Select cell B5; The formula bar will display ='[XYZ Office Budget.xlsx]Budget'!$B$5-'[XYZ Actual Expenses.xlsx]Actual Expenses'!$B$5 (a handleful!)
·         Press enter
And now cell B5 contains the difference between the contents of cells in different files.
The main difference between the previous example and this one is the name of the file, which is pre-appended to the sheet’s name inside square brackets. The whole sheet name (workbook name in square brackets and worksheet name) is quoted together.

Relative of Absolute

Another difference is that Excel initially assumes that referencing other sheets in the same file is relative to the current cell, but referencing other workbooks is absolute, as is apparent by the $ appended to the row and column of the cells we selected: $B$5 instead of B5.
However, we would like to use the fill handle to quickly deal with the table, so we need to change the references to be relative. To do so, we just erase the ‘$’ signs from the formula:
='[XYZ Office Budget.xlsx]Budget'!B5-'[XYZ Actual Expenses.xlsx]Actual Expenses'!B5
Now grab the fill handle and mark the whole row, and then the whole table:
Summary
In this section of the multiple sheets and files linking article, we discussed combining data from multiple files (after going through linking sheets within the same file in Formulas for linking data between different sheets). Referring to data from other Excel files can be extremely useful in a lot of cases; for example, we can create a summary file using data from multiple Excel files.
In the third part of the article, we’ll look at how you can use Excel the combine data from multiple sheets without having to reference them all!
About the Author: Joseph Reese is the founder of Excel-Formulas.com an Excel consulting firm. Head over to his website if you want to improve your performance with Excel.



0 comments:

Digg Facebook Technorati Delicious StumbleUpon Reddit BlinkList Furl Mixx Google Bookmark Yahoo Add to Technorati Favorites TwitThis

Post a Comment