Three sheets, one story
The model on Kapesne calculates the balance. Grades on Znamky have an average. The survey on Pruzkum has a winner. Linking sheets = formulas like =Znamky!B8, not Ctrl+C of a number.
The Modeller builds a network of links. The Workbook boss will put a dashboard together from that — today the first bricks.
Links between sheets
-
A new sheet Prehled (or expand a
Poznamkysection at the top). A1Grade average, B1=Znamky!B8. -
A2
Spending week, B2=SUMA(Kapesne!C2:C8). -
A3
Survey winner, B3 — by hand for nowrollor a formula later. A4Winner votes, B4=Pruzkum!E2(when roll is in D2). -
Change a grade on
Znamky. B1 on Prehled moves. If not, B1 is not a formula — fix it. -
Format B1 number 1 decimal, B2 currency Kč. Prehled is a mini-dashboard without a chart.
You write = and click a cell on another sheet — the table fills in Znamky!B8 itself. Typing the sheet name by hand is OK when you type it exactly.
A path to XLOOKUP across sheets
=XLOOKUP("roll";Pruzkum!D:D;Pruzkum!E:E) in B4 — winner votes without handmade E2. When the winner changes, you have to change the looked-up text — later INDEX+MATCH on MAX.
What often goes wrong
A copy instead of a link. B1 = 1.8 hardcoded. A grade change Prehled does not see.
Wrong sheet name. A sheet renamed to Známky with an accent — formula #REF!. Sheet names without accents is fine (Znamky).
A link to the average in a chart. The chart takes data from sheet Znamky. Prehled only shows a number.
🏫 Grade average — =Znamky!B8
The only average in B8. Prehled, later Dashboard — everywhere a link. Nowhere a second PRŮMĚR by hand.
💰 Pocket money — SUMA across
=SUMA(Kapesne!C2:C8) on Prehled. Scenario F2 does not change actual C — Prehled shows what was, model H shows what if.
📊 Class survey — column E
A link to the winner cell in summary D:E. When COUNTIF recalculates, E2 fits.
🏆 Mini dashboard — Prehled = a prototype
Sheet Prehled is dashboard practice. A1:B4 the same logic as Dashboard at the Boss — three numbers, no raw rows.
Now you 💪
First: sheet Prehled with three links (average, spending, votes).
Second: change data on the source sheets — Prehled reacts.
Check: B1 and B2 are formulas with !. No #REF!. Formats correct.
What to take from this lesson
- Linking =
=Sheet!Cell, not a copy. - Prehled = the first layer of a dashboard.
- Reality (C) vs. model (H) — Prehled from C.
© 2026 Ing. Martin Polak / AlgoRhino · Ketentuan penggunaan konten