A formula across sheets · 마틴과 코딩하기

A sheet is a name in the address

A formula can reach another sheet in the same workbook:

=AVERAGE(Grades!B3:B10)

Grades = the sheet name. An exclamation mark ! separates the sheet and the cell. In Excel the same: Sheet!A1.

💡

Type = and click another sheet and cells with the mouse. Sheets writes Grades!B3:B10 itself. Spaces in a sheet name give apostrophes: 'My sheet'!A1.

The dashboard shows the grade average

Sheet Dashboard (from Cell kid). B3 empty — it was meant for the average.
Click B3. Type =. Switch to sheet Grades. Select grades B3:B8 (data only).
Enter. In B3 on the Dashboard the average lights up. In the formula bar =AVERAGE(Grades!B3:B8).
Change a grade on Grades. The Dashboard recalculates.

⚠️

Renaming a sheet. When you rename Grades to Grades_march, a formula with Grades! shows #REF! / a broken reference. Rename on purpose, or fix the formula.

A reference to one cell

B5 Dashboard Favourite snack — text for now. B6 can be =Survey!A3 — the first snack in the list. That is only a sample of a reference, not “best” logic.

A better sample: B4 =SUM(Pocket!B2:B8) — the week’s total income on the dashboard.

Three numbers at the top — without copying by hand

Dashboard	Formula (example)
Grade average	`=AVERAGE(Grades!B3:B8)`
Income week	`=SUM(Pocket!B2:B8)`
Votes total	`=SUM(Survey!B3:B7)`

Labels in A. Formulas in B. Do not type 1.8 by hand when it lives on Grades.

Dashboard B3, B4, B5 — three formulas pointing at three sheets.
Click each. The formula bar must contain ! and a sheet name.

🏫 Grade average

One sheet, two columns: subject and grade. At the bottom a sum and an average.

💰 Pocket money

Rows = days, columns = income / spend / left.

📊 Class survey

One question, answers in one column, later a chart.

🏆 Mini dashboard

Three numbers at the top and one chart. Numbers as a link, not copied by hand.

Now you 💪

The Dashboard has at least two formulas with a Sheet! reference.
A change on Grades changes the number on the Dashboard.
You do not rewrite by hand what another sheet already counts.
You know that ! separates a sheet and a cell.
You make a reference by clicking the mouse on the other sheet.

✅

Check: The mini dashboard starts to live — numbers are links, not copied.

What to take from this lesson

Sheet!B3 = cell B3 on sheet Sheet.
One workbook, more sheets — formulas between them.
A dashboard = a summary of links.
Renaming a sheet breaks old formulas.
A mouse click on another sheet writes the address.