21. Real-World Project Briefs

21.10 Multi-Outlet Roll-up

By Ravindra Bagale · Last updated: 6 October 2026

The ask. Rani receives four sheets every month, one per outlet (Pune, Nashik, Nagpur, Kolhapur), all with the same layout. She needs a Summary sheet that adds them up, plus each outlet's share of sales, without copy-paste.

Each outlet sheet has the same layout: B2 = Sales (₹), B3 = Orders, B4 = Expenses (₹). The sheets sit next to each other from Pune to Kolhapur. Fictional:

What to deliver (one under the other):

1. Keep the outlet sheets side by side, Pune first and Kolhapur last.
2. On Summary, use a 3D SUM across the sheets for Sales, Orders and Expenses.
3. Add a per-outlet table with INDIRECT so the sheet name comes from a cell.
4. Add Share of sales = outlet sales ÷ total sales.
5. Add AOV = Sales ÷ Orders for each outlet and the total.
6. For a long-term solution, append the four sheets with Power Query instead (Module 10).

Key formulas (row 2; copy down or let the Table fill them):

Summary!B2: =SUM(Pune:Kolhapur!B2)
Summary!B3: =SUM(Pune:Kolhapur!B3)
Per outlet (A7 = Pune): =INDIRECT("'"&$A7&"'!B2")
Share: =B7/$B$2
AOV: =ROUND(B7/C7,0)

Expected result (check your numbers):

Total expenses = ₹62,800. The 3D SUM gives the same totals as adding the four sheets by hand.

Checks before you send it:

1. All outlet sheets have exactly the same layout (same cells for the same numbers).
2. A new outlet sheet placed between Pune and Kolhapur is included automatically.
3. Shares add up to 100.0%.
4. Sheet names with spaces are wrapped in single quotes inside INDIRECT.