Ravindra BagaleCourses & study guides मराठी Track your progress

23. Formulas Across Sheets and Workbooks

23.3 Referencing an Excel Table on Another Sheet

By Last updated:

Example first. The same orders, but now as a Table named tblMini on the Orders sheet. On City Summary the formula is =SUMIFS(tblMini[Amount],tblMini[City],A2). There is no sheet name in it, and when BLK-1011 is added, Pune becomes ₹844 by itself.

Table reference across sheets — in 30 seconds

What: A Table name works from every sheet in the workbook. tblMini[Amount] means the Amount column of that Table, wherever the Table is and however long it grows.

Why: The formula reads like English, needs no $ signs and never misses a new row. Moving the Table to another sheet does not break it.

Tiny example: =SUMIFS(tblMini[Amount],tblMini[City],A2) gives ₹544 for Pune, and ₹844 after the new Pune order is added under the Table.

First look at the data (before → after):

Table reference across sheets: before and after tables, fictional Blinkit Maharashtra data

Expected result — City Summary after the new order (Table version)

City Sales (₹) Orders
Pune 844 5
Nashik 360 2
Nagpur 240 1
Kolhapur 232 1
Solapur 1299 1
Sambhaji Nagar 195 1
Total 3170 11
Before table and formula as text

Before — Orders sheet: tblMini plus a new row 12 (BLK-1011, Pune, ₹300)

Order ID City Amount (₹)
BLK-1001 Pune 64
BLK-1002 Nashik 270
AMN-1003 Pune 110
BLK-1004 Nagpur 240
AMN-1005 Kolhapur 232
BLK-1006 Pune 150
BLK-1007 Solapur 1299
AMN-1008 Sambhaji Nagar 195
BLK-1009 Pune 220
BLK-1010 Nashik 90
BLK-1011 Pune 300

Formula / click path

City Summary!B2: =SUMIFS(tblMini[Amount],tblMini[City],A2)
City Summary!C2: =COUNTIFS(tblMini[City],A2)
Grand total anywhere: =SUM(tblMini[Amount])

Do it step by step:

  1. Click inside the orders on the Orders sheet and press Ctrl + T. Name the Table tblMini.
  2. On City Summary, click B2 and type =SUMIFS(tblMini[. Pick Amount from the list and type ],.
  3. Type tblMini[City],A2) and press Enter. Fill down to B7.
  4. Go back to Orders and type the new order in the first row under the Table.
  5. Return to City Summary. Pune now includes the new order.
  6. Cut the whole Table and paste it on a new sheet. The City Summary formulas still work.

Range vs Table across sheets

Normal range Excel Table
Formula =SUMIFS(Orders!$G$2:$G$11,Orders!$C$2:$C$11,A2) =SUMIFS(tblMini[Amount],tblMini[City],A2)
Needs the sheet name Yes No
Needs $ signs Yes No
New row under the data Missed Included
Data moved to another sheet Still works if you cut and paste; breaks if you retype Always works

One limit to know. Structured references cannot point at a Table in another closed workbook in a way that keeps working; Excel turns them into plain cell references in the external link. For links between files, use Power Query (Module 10) or the methods in 23.6.

Practice task (try it before you open the answer):

  1. Convert the orders to tblMini and build City Summary with structured references.
  2. Add two orders: BLK-1012 Nagpur ₹410 and AMN-1013 Kolhapur ₹150.
  3. Read Nagpur, Kolhapur and the grand total.
Answer

Nagpur ₹650, Kolhapur ₹382, grand total ₹3,430 (without the 23.3 example order).

Ravindra Bagale's Tip

If a formula on a report sheet needs a sheet name and $ signs, ask whether the data should be a Table. Nine times out of ten, it should.