Lookup & Reference
3D references: sum the same cell across consecutive monthly worksheets
Consolidate identical template cells across tab ranges like Jan:Dec!B5 using 3D worksheet references.
Consolidating identically structured ranges with 3D references
A 3D reference such as Jan:Dec!B5 or Sheet1:Sheet1!B2:B4 spans a contiguous sequence of worksheet tabs and aggregates the identical cell or range across every included tab.
Keep row and column coordinates identical across each monthly or regional block so inserting or updating values inside the span automatically updates the consolidated total.
| Period Row | Cell | Revenue | Included in 3D Span |
|---|---|---|---|
| Jan Block | B2 | 1200 | Yes |
| Feb Block | B3 | 1500 | Yes |
| Mar Block | B4 | 1800 | Yes |
=SUM(Sheet1:Sheet1!B2:B4)Guarding against missing or negative period entries
Verify that all expected period rows contain numeric entries with COUNT before reporting the consolidated total.
=IF(COUNT(B2:B4)<3,"Missing period",SUM(Sheet1:Sheet1!B2:B4))Try the example
Use the original values or change them to test your understanding.