Working with Multiple Spreadsheets

Earlier in the book, I mentioned that you may have multiple spreadsheets in the same workbook, and that you can use data from other spreadsheets in your formulas. The formulas can refer to other sheet tabs by adding the sheet name to the cell reference. With a further addition you may even refer to cells in completely different workbooks!

I know a medium sized company, which does its entire budget in spreadsheets. When it is completely finished, it is loaded into their finance system. The advantage is that it is easier to juggle with numbers in spreadsheets than in a large ERP system. The company believe themselves that they make more precise and thorough budgets in this way. Each department as a starting point make their own budget in a spreadsheet template developed by the Finance Department. Naming and placement of worksheets/Workbooks has been agreed in advance.

Once all departments have reported that their respective sub-budgets are finished, the finance department opens a special spreadsheet that pulls data from all sub-budgets and transform them into a format that can be loaded into the finance system.

This is a procedure that Works, but it requires great discipline. All spreadsheet files must be named correctly and placed in the right folder on the corporate network.

1.1 Working with Sheet Tabs

If you make a large spreadsheet with many contexts, it can be very confusing if you have it all in a single spreadsheet. We therefore use a "workbook" with sheet tabs, each sheet tabbeing a spreadsheet itself. As We have seen, a sheet tab can also include a sheet where you have a chart instead of a spreadsheet.

• Start a new spreadsheet. 
• Right-click on "Sheet1" at the bottom of the screen and click on Rename. 
• Type Input and press ENTER.

Rename Sheet Tabs.

You have now re-named Sheetl, so it reads "Input" instead. It could also have been called "Grandma" if it was what you wanted.

• Type 2 in cell B2. 
• Click on the sheet tab Sheet2. 
• Type 3 in cell B2.

We will now type a simple formula in Sheet2 that adds together the contents of the two cells.

• In cell B3 in Sheet2, type an = sign to start a new formula. Do not press the ENTER key. 
• Click on the sheet tab Input.
• Click on cell B2 (the one with the number 2 in it).
• Press the + key on the keyboard.
• Click on the sheet tab Sheet2.
• Click on cell B2 and press the ENTER key.

The result of the formulais5, which you probably had figured out from the start. What is more interesting is the actual formula. If you make cell B3 in Sheet2 the active cell, you can see in the formula bar that the formula is:
"=Input! B2+Ark2TB2

So, you put the sheet name first, write an exclamation mark and then the ordinary cell reference. If you look closely at the two references, you may detect a slight difference. "Sheet 2" is surrounded by single quotation marks, but "Input" is not. It is because there is a number 2" in the sheet name. If there are numbers or spaces in the sheet name, they should always be surrounded by single quotation marks. The

name "Input has neither and thus gets no quotation marks.

You can leave this workbook open because we are going to need it for the next exercise.

1.2 Retrieve Data from Other Workbooks

As mentioned, you can retrieve data from other workbooks (Excel files). It takes only a reference to the

file in the cell reference.

• Save the current worksheet in the folder "My Documents. Call the file "test klsk".
• Start a new spreadsheet, but leave test.xls x open.

Now we will make the same calculation as in the previous exercise, but we make the calculation in the new workbook.

It does not matter what sheets you use in the new workbook. In this exercise, the important thing is the

references to test.xls x.

• In cellB2, type an equal sign to start a formula. 
• In the Ribbon, select the View Tab and click on the Shift Window button. 
• In the menu that appears, choose the menu item test klsy.

You are now back in the old spreadsheet.

• Click on the sheet tab Input.
• Click on cellB2. 
• Press the + key on the keyboard. 
• Click on the sheet tab Sheet2. 
• Click on cellB2 
• Press the ENTER key.

Now you are back in the new spreadsheet again, and the formula should give the result 5 again. But again, you have to look at what is in the formula. It probably says:

=test disk Inputts BS2+ test dsx) Ark2lsBS2

Now the file name has been inserted at the front of the references in square brackets. It is really very simple, but there is a little extra detail. When referring to other workbooks, Excel automatically uses absolute references, as you can see because dollar signs have been inserted. You have to be aware of that if you need to copy the formulas.

Another detail is that the references only look like the example when test dsx is open. When test.xlsx is closed, the path is also there.

• In the Ribbon, select the View Tab and click on the Change Window button. 
• In the menu that appears, choose the menu item test.xlsk. 
• Click on the Office Button and choose close. If asked if you want to save answer Yes. 

Go back to the formula in the new folder. It is now:
="C:\DOKumenter testixis x) InputsBS2+CADOKumenter test.xlsx)Ark2lsBS2
It is handy that you can link multiple workbooks, but be aware that if you rename or move source files to another folder, the formula that retrieves data from them, should be changed accordingly, otherwise it will not work.

Thus it requires a bit of discipline to workin this way.