Chapter 7 continued ...
Creating a link between cells in a workbook
Create a link between two workbooks. In Cell A1 in one workbook, enter a formula that links to Cell A1 in another open workbook.
There are two ways to create links between cells. The first method is between sheets in an active workbook, and the second is between a sheet in one workbook and a sheet in another workbook. Here are two methods for setting up a link between workbooks. The second method is rarely used, but easier.
Method 1 Starting in the destination workbook:
In Cell A1, type the = sign. From the Window menu, select another workbook, select Cell B1 in one of its sheets, and press Enter.
Method 2 Starting in the source workbook:
Go to the source workbook. Copy Cell B1, and press Ctrl+Tab (to move between open workbooks). Select Cell A1, right-click, and from the shortcut menu, select Paste Special. Click Paste link.
Auditing and tracing precedents (cells that contain formulas) or dependent cells (cells that are referenced by formulas) can be a big headache for the Excel user.
Using Names (see Relative reference and Absolute reference) is very helpful in reducing the time spent auditing and evaluating precedent cells. This section will show you ways to audit and evaluate formulas.
In Excel 2002, the relevant toolbar is called Auditing formulas. In Excel 97 and 2000, the toolbar is called Auditing.
Select one of the toolbars, and right-click. From the shortcut menu, select Auditing formulas.
In Excel 97 and 2000, the Auditing toolbar does not appear in the list of toolbars. Select one of the toolbars, right-click, and from the shortcut menu, select Customize. Select the Toolbars tab, and select the Auditing toolbar (or from the Excel menu, select Tools, Auditing formulas).
Moving to a precedent cell / moving to a dependent cell
Select Cell A1 and click the Trace precedents icon on the Auditing Formulas toolbar. Double-click the dotted arrow, and in the Go to dialog box, select the reference to which Cell A1 is linked. Click OK. See the figure below.
Select Cell A1 (without a formula), and click the Trace dependents icon on the Auditing formulas toolbar. Double-click the dotted arrow, and select the cell being referenced. Click OK.
Moving between linked cells in a sheet
Select a cell with a formula that is linked in the same sheet (for example, =B10). Click the Trace precedents icon on the Auditing formulas toolbar. Double-click the blue arrow between the linked cells to move between the precedent cell and the dependent cell.
Keyboard shortcuts to precedent and dependent cells
Precedent cell – Ctrl+[
Select all precedent cells – Ctrl+Shift+{
Press F2 or double-click a cell that contains links within the sheet. The precedent cells are indicated in blue.