Learning Objectives

  • Reference cells on other worksheets (=Sheet2!B5)
  • Build a Dashboard that pulls Pivot and Employees totals
  • Create and manage external workbook links
  • Use 3D references across similarly structured sheets
  • Update or break links safely before submission

Same workbook:

=Employees!G12
=Pivot!B4
='Sales Data'!D10

If the sheet name has spaces, wrap it in single quotes.

Dashboard pattern (Model Lab Test Set B style)

  1. Sheet Dashboard.
  2. Label cells in column A (Total Gross, West Sales, …).
  3. In column B, pull live values from Employees / Pivot / Sales.
  4. Add a chart that uses Dashboard values or a dedicated chart sheet.
  1. Open source and destination files.
  2. In the destination, type = and click a cell in the source workbook.
  3. Excel inserts a path like:
='[Budget_2026.xlsx]Summary'!B10
  1. Save both files in the same project folder before distributing.

Data → Edit Links (Windows Excel):

  • Update Values
  • Change Source
  • Break Link (converts to static numbers — irreversible)

3) 3D References

When Jan–Dec sheets share the same layout:

=SUM(Jan:Dec!B2)

Useful for monthly department totals without listing twelve sheets.

4) Risk Management

Risk Mitigation
Moved/renamed source file Keep a stable folder; use Edit Links → Change Source
Broken #REF! after sheet rename Rename sheets carefully; update formulas
Sharing only the Dashboard Break links or send the whole folder
Circular references Dashboard should pull values, not push back into source

Practice Task

  1. Create Jan, Feb, Mar sheets with the same total cell B10.
  2. Summary sheet: =SUM(Jan:Mar!B10).
  3. Dashboard sheet linking Employees Net total and Pivot regional total.
  4. Optional: link one cell from a second workbook, then demonstrate Update and Break Link.

📚 Practice: Unit 3 Practical Assignment