What you will learn:
to create a link
to use Paste Link
to correct a linking formula
trips19.xls - Specials & Tahiti (saved in
To have the data on the sheet Tahiti updated whenever the original on the
sheet Specials changes, you must link the cells.
In the Formula bar =Specials!A4 would
link that cell to cell A4 on the Specials sheet. Note the exclamation point
between the sheet name and the cell reference. You can do this yourself if
there are just a few cells. For many cells you can use Paste Link on the Paste
Special dialog to do them when you paste.
When you link cells, you do not get the formatting also. You must apply
the formatting separately.
- Select cell A1 on the sheet Tahiti.
Type =Specials!A1 and click
the green check mark. The cell A1
is still selected and looks the same as before, but now, if you change
cell A1 on the sheet Specials, this cell will change, too.
This is the purpose of linking.
- Switch to the sheet Specials and edit cell A1
in some way.
- Return to Tahiti to see if its cell A1 changed
to match. (It should have!)
- Use Undo to return cell A1 remove your changes. (You do not
have change sheets first. There is only one Undo list.)
Link by mouse:
- Select cell A2 on sheet Tahiti. Clear the
contents. ( | | ). The cell is now blank and is no longer
Type an equals sign =
- Switch to sheet Specials and click on cell A2.
- Press ENTER. You are switched back to the sheet Tahiti. The
text Anniversary Specials shows up, but there is no formatting.
- Select cell A2 again. The Formula
bar shows = Specials!A2 for the
contents of cell A2.
This mouse method of linking cells avoids the problem of fumble fingers not
typing the name of the sheet or the cell reference correctly. It would get
tiresome to do this for every cell in the table, however. Redoing the
formatting would certainly get old in a hurry. Next we will look at a
method for linking cells and getting the same formatting. It's awkward,
but it works.
forget to press ENTER after selecting the cell to link, your next clicks
also put cell references in the Formula bar. Quite a mess. The Undo
command is a real help when this happens.
Link: Paste Link
Using not copy the formatting of the original cells. But a second
use of Paste Special with Formats selected will copy just
to link a number of cells
- Clear table: On the sheet Tahiti, select the whole table, A1:F11, and
clear it with |
| . You are back to a blank sheet.
- Switch to sheet Specials and select ranges A1:F10
and A25:F25 (the data about Tahiti trips) again and Copy.
to sheet Tahiti and select cell A1.
- Select from the menu |
and click the button
Paste Link. The non-adjacent data you copied is pasted on the sheet Tahiti
as adjacent cells.
- Click on several cells in the paste area and check what the
Formula bar shows.
All the cells in the table on Tahiti are linked to the
cells in Specials.
- No formatting was copied with the linking.
- A zero is shown when the original cell was blank.
- Totals are incorrect for this table.
In Excel 97 - Row 11 is supposed
to be linked to cells in Row 25 on the sheet Specials. Instead Row 11
cells are linked to Row 35! This is a serious glitch, caused by
linking ranges that are not adjacent to each other in the original
sheet. Excel is really confused! Therefore:
Don't try to Paste Link non-adjacent ranges in Excel 97. Copy each range separately.
In the next step you will fix Problem a. You will fix problems b and c
by editing the cell references by hand later.
- Fix Problem a- Lost Formatting:
Select A1 on the Tahiti sheet and from the menu
and choose Formats
in the dialog. Click OK. AutoFit column F to show
the entire total.
If the Paste Special...
command is grayed out or you get the wrong dialog, the Clipboard has
lost your copied data. You have done something since you pasted last.
Return to sheet Specials, copy the cells (they should still be
selected), and try again.
So, oddly enough, for non-adjacent ranges you can Paste
Special the formats accurately, but you can't Paste Link the cells
accurately. Just remember, if you forget this difference later, it's not
you that's crazy. It's Excel!
Fix Problem b
- Zeros in blank cells: Delete the zeros from all Tahiti sheet cells that should
be blank- all cells in row 3, A11, B11. Delete the contents of cell
Repair a Formula
Something odd is going on with the totals cells. These totals cells show the sums for
the whole column on the sheet Specials. This is not what you really
want. You want the sum of the values that are on this sheet. So
these totals cells should not be linked after all.
Select cell D11 and click on the AutoSum button
and press ENTER.
- Repeat for cell F11.
- If necessary, resize Row 4 to just fit the height of the labels.
Save as trips20.xls on your Class disk in the excel project 3
How to handle a full disk
Test the links
- Test the links by changing some values for the Tahiti trips on
Specials, and then looking at the Tahiti sheet to see if the changes
are shown there. Change the names as well as the number of people and
the price per ticket.
- Undo all your experiments! (Remember- You saved before you started
your experiments. You can close the file without saving changes and open
it up again to get back to where you were.)
- With sheet Tahiti active, open Print Preview. There is no
header! Sadly, you must do a header separately for each sheet in a
on the Setup… button and then the Header/Footer tab.
- Create a custom header for the Tahiti sheet - Left section has
your name and the date; Center section has the file name and sheet name;
Right section has Excel Project 3. Click OK to close the dialog.
- Make corrections to the sheet if necessary. Close Print Preview.