External Reference Links: Link Excel Worksheets Together

Worksheets can be linked so that information shared between the two is always identical. This reduces errors and ensures consistency as information only needs to be entered in one place to be available in multiple locations.

In our example today, data is gathered and duplicated between employees and departments. One employee compiles a sheet with donation information from several departments. One department tracks thank-you notes, another creates press releases and a third performs analysis for fundraising. Rather than having your volunteer re-enter the data each time it changes, you want a way for the related sheets to share the necessary information to the summary sheet.

Link Worksheets

1. Open both the source and destination (dependent) workbooks.

2. In the dependent workbook, select a cell where you want to place the formula that will link that cell to the source data.
3. Enter the formula up to the point where you would refer to the source cell.

(In this example, you want to see if the donor has received a thank you note by referencing the Thank You Card Date in the Donor Thank You workbook. The COUNT function will return a value of “1” if a date has been entered or a “0” if a date has not been entered.)

4. Switch to the source workbook and select the cell or data range you need for the formula in the dependent workbook. Excel recognizes you are creating a link and displays the formula in the formula bar.

5. Return to the destination workbook and complete the formula.
6. Press the Enter key on your keyboard to see the cell populated. In this example a “Y” should appear if the donor has received a card, and an “N” if they have not.

7. Repeat as necessary to complete your links. Note that any cells dependent upon your linked cell will also compute and populate.

If you plan to apply your links to an entire column of data, change the cell references from absolute to relative and simply fill down the column. Your sheets must appear in the same sort order to do so!