As of this writing, 3-D references are not supported in Google Sheets. To achieve similar functionality, you may need to use a third-party add-on or a workaround formula.

In this tutorial, we will use the REDUCE LAMBDA function to create a formula and a custom function that enables 3-D referencing in Google Sheets.

A 3-D reference is particularly useful when working with a Google Sheets file (workbook) containing several sheets of similar data. For example, you might have account statements for outstanding liabilities across different cost centers.

When you want to total these liabilities, you might think of using a 3-D reference like =SUM(Sheet1:Sheet5!C10). However, this approach won’t work in Google Sheets, so we will adopt a workaround.

Please note that the flexibility you may find in Excel with 3-D references may not fully translate to Google Sheets using our workaround formula. For instance, you will need to adhere to a specific naming pattern for your sheets (tabs). For example, you could name your sheets “Sheet1”, “Sheet2”, “Sheet3”, or use a quarterly naming scheme like “Q1”, “Q2”, “Q3”.

Examples of 3-D References in Google Sheets

We can utilize either a custom function or a formula to create a 3-D reference. The custom function will be much easier to use, so I’ve coded a function named _3D.

Let’s start by understanding its usage.

You can use the following formula to add up outstanding liabilities across five cost centers, each on a different worksheet:

=SUM(_3D("OS ", "C6", 5))

In this example, "OS " is the common tab name, "C6" is the cell reference, and 5 represents the range of sheets from 1 to 5.

This formula will yield the same result as the following:

='OS 1'!C6 + 'OS 2'!C6 + 'OS 3'!C6 + 'OS 4'!C6 + 'OS 5'!C6

Here’s an example of a 3-D reference using a REDUCE-based formula that aligns with the _3D custom function in Google Sheets:

=SUM(
   REDUCE(
      TOCOL(,1), 
      ARRAYFORMULA("OS "&SEQUENCE(5)&"!"&"C6"),
      LAMBDA(a, v, IFNA(VSTACK(a, INDIRECT(v))))
   )
)

Handling Cell Ranges in a 3-D Reference

My custom function or formula can handle cell ranges effortlessly. Here are two examples:

=_3D("OS ", "B3:C5", 5)

This formula will return the range B3:C5 from all five sheets that start with the tab name ‘OS’, stacked vertically.

Here’s an alternative formula that achieves the same result without using the _3D custom function:

=REDUCE(
   TOCOL(,1), 
   ARRAYFORMULA("OS "&SEQUENCE(5)&"!"&"B3:C5"),
   LAMBDA(a, v, IFNA(VSTACK(a, INDIRECT(v))))
)

Note: You can also use functions like QUERY or others to manipulate the results of the 3-D range reference.

Syntax and Arguments for the _3D Named Function

Syntax:

_3D(sheet_name, cell_range, total_sheets)

Arguments:

• sheet_name: The name of the sheets without the serial number. For example, if your sheets are named “Expense 1”, “Expense 2”, “Expense 3”, and “Expense 4”, then the sheet_name would be "Expense ". If the sheets are named “Expense1”, “Expense2”, etc., then the sheet_name would simply be "Expense".
• cell_range: The cell or range reference to use in the 3-D reference. For example, you could specify "C5" or "C5:N100".
• total_sheets: The total number of sheets to use. For example, if you have 4 sheets, then total_sheets would be 4.

FAQs

Q: How do I start using the _3D named function in my Google Sheets?
A: You need to import the function from the sheet linked below, at the end of this tutorial. You can find the instructions here: How to Create Named Functions in Google Sheets.

Q: Which functions support 3-D references in Google Sheets?
A: You can use most aggregation functions, lookup functions, QUERY, FILTER, SORT, LET, etc., with 3-D references. The specific functions that work depend on the values in the range you are referencing.

Q: Why does my _3D formula convert dates to numbers?
A: This occurs because the IFNA function used in the custom function returns date values as numbers. To format them back to dates, select the cells and go to Format > Number > Date.

The Function Behind 3-D References in Google Sheets

If you’re not particularly interested in the technical details, you can skip this section.

The earlier examples demonstrate that the REDUCE function serves as the core component behind the _3D named function.

Generic Formula:

REDUCE(
   TOCOL(,1),
   ARRAYFORMULA("sheet_name"&SEQUENCE(total_sheets)&"!"&"cell_range"),
   LAMBDA(a, v, IFNA(VSTACK(a, INDIRECT(v))))
)

REDUCE Syntax:

REDUCE(initial_value, array_or_range, lambda)

Where:

• initial_value is set to null using a TOCOL formula.
• array_or_range is generated using an ARRAYFORMULA and SEQUENCE combination to list the sheet names along with the range.
• lambda: The INDIRECT function retrieves the values in the specified range from the first sheet in the list in array_or_range. It is vertically appended to the initial value using the VSTACK function. The REDUCE function then continues this process for each subsequent sheet until it reaches the last sheet in the array.

That’s all about 3-D references in Google Sheets. Thank you for reading, and enjoy exploring this functionality!

Download _3D Function Template

Resources

Discover how to effectively use 3-D referencing within structured data tables in Google Sheets. It’s a built-in feature, not a workaround involving a custom function.

• 3-D Referencing Structured Data Tables in Google Sheets