Print

How to carry over a value from another spreadsheet

Microsoft Excel Tips

Why is it called a Workbook?

Before we begin it would be wise to establish some basics about an Excel file. When you open up a new file you are creating aWorkbook- a collection of one or more spreadsheets.

When you open up a new file notice the little tabs at the foot of the screen labelled Sheet1.  You can add another spreadsheet within the Workbook by clicking the + here...

excel add sheet

You should new see two spreadsheets like this...

excel sheet1 sheet2

You can type values, text and formulae in each Sheet and switch between them by clicking the tabs.

Linking Sheets

Say you had a formula that adds up the results of a column on Sheet1 and you wanted to carry over that result into a cell at the top of Sheet2.

linking spreadsheets

This is how you do it.

1. Click once in the cell on Sheet2 (where you want the resulting amount to be displayed from Sheet1)

2. Type (an equals symbol just left of the backspace key)

3. Now click on the Sheet1 tab and click once on the cell that has the value you wish to bring over (e.g. the total)

4. Now Press the Enter Key on your keyboard

Now wasn't that easy?

What about displaying a whole row?

Say you had a row of values going across 10 adjacent cells on Sheet1. It would be a bit of a chore to use the technique above ten times to display them all on Sheet2.

This is what you can do instead:

1. Select (by clicking and dragging across) the ten cells in Sheet1

2. Copy (Ctrl+C) (you will see 'marching ants' around your selection)

3. Click on the Sheet2 tab and click in the first left hand empty cell

4. Right Click on the same cell and select Paste Special

5. Click the PASTE LINK button at the bottom

Hey Presto!

Now try changing values on Sheet1 and check Sheet2 - it will automatically change.