23. Formulas Across Sheets and Workbooks
Practice and Answer Key: Quarter Report Across Sheets
Goal of this practice: Build a quarter report from three monthly sheets with sheet references, a 3D SUM and an INDIRECT month picker.
या practice चं ध्येय: तीन monthly sheets मधून sheet references, 3D SUM आणि INDIRECT month picker वापरून quarter report बनवा.
इस practice का लक्ष्य: तीन monthly sheets से sheet references, 3D SUM और INDIRECT month picker से quarter report बनाएँ.
Suppose Zoya runs three fictional outlets: Pune, Nashik and Solapur. She has one sheet per month, Apr, May and Jun, each with the outlets in A2:A4 and sales in B2:B4. All data is fictional practice data.
| Outlet | Apr (₹) | May (₹) | Jun (₹) |
|---|---|---|---|
| Pune | 3,10,000 | 3,34,000 | 3,51,000 |
| Nashik | 2,05,000 | 2,19,000 | 2,26,000 |
| Solapur | 1,72,000 | 1,68,000 | 1,81,000 |
Exercise (one under the other):
- Create sheets Apr, May and Jun with the numbers above (outlets in A2:A4, sales in B2:B4).
- On a Summary sheet, show May vs Apr growth for Pune with a cross-sheet formula.
- On a Q2 sheet, add each outlet across the three months with a 3D SUM.
- Add a grand total with one 3D SUM over B2:B4.
- On a Report sheet, add a month drop-down in B1 and a total with INDIRECT.
- Consolidate the three sheets with Function: Max to see each outlet's best month value.
Expected result (check your numbers):
| Check | Expected |
|---|---|
| Pune growth May vs Apr | 7.7% |
| Q2 Pune / Nashik / Solapur | ₹9,95,000 / ₹6,50,000 / ₹5,21,000 |
| Q2 grand total | ₹21,66,000 |
| INDIRECT total for Apr / May / Jun | ₹6,87,000 / ₹7,21,000 / ₹7,58,000 |
| Consolidate Max (Pune / Nashik / Solapur) | ₹3,51,000 / ₹2,26,000 / ₹1,81,000 |
Answer key (open after you try)
Summary!B2: =May!B2/Apr!B2-1
Q2!B2: =SUM(Apr:Jun!B2) (fill down to B4)
Q2!B5: =SUM(Apr:Jun!B2:B4)
Report!B3: =SUM(INDIRECT("'"&$B$1&"'!B2:B4"))
Consolidate: Data › Data Tools › Consolidate › Function: Max › Apr!$A$1:$B$4, May!$A$1:$B$4, Jun!$A$1:$B$4 › Left column
Common mistakes:
| What you see | Why | Fix |
|---|---|---|
| 3D SUM total is too high | Another sheet sits between Apr and Jun | Move it outside the group, or use Start/End sheets |
| #REF! in the INDIRECT cell | B1 has a month that is not a sheet name, or the quotes are missing | Use the drop-down and keep "'"&$B$1&"'!" |
| Consolidate shows numbers without names | Left column was not ticked | Delete the result and run it again with Left column ticked |
| Growth shows 107.8% instead of 7.8% | The -1 is missing | Use =May!B2/Apr!B2-1 |
Ravindra Bagale's rule: same layout → 3D SUM; different layout → Consolidate; every month → Power Query.
Quick recap
Sheet!Cellreads another sheet; quotes are needed when the name has a space.- A Table name works from any sheet and grows with the data; a normal range needs $ and misses new rows.
=SUM(Jan:Mar!B2)adds the same cell across sheets; it does not work with SUMIFS or XLOOKUP.- INDIRECT turns text into a reference; it breaks on renamed sheets and closed files and is volatile.
- External links: build them by clicking, manage them in Data › Queries & Connections › Workbook Links, break them before you email.
- Consolidate matches by label when sheets have different layouts.
Loading practice tasks...
- CRM Pro in April (Easy): Get Apr!C2.
- Total April revenue (Easy): Total of Apr!C2:C4.
- Total June licences (Easy): Total of Jun!B2:B4.
- HR Suite in May (Easy): Get May!C3.
- CRM Pro quarter (Medium): Apr!C2 + May!C2 + Jun!C2.
- Cloud Backup quarter (Medium): Apr!C4 + May!C4 + Jun!C4.
- Growth Apr to Jun (Medium): Total Jun revenue minus total Apr revenue.
- Best month for HR Suite (Medium): Biggest of Apr!C3, May!C3, Jun!C3.
- Quarter revenue (Hard): Total revenue Apr + May + Jun.
- Average price in May (Hard): Total May revenue ÷ total May licences.
- CRM share of June (Hard): Jun!C2 ÷ total June revenue.