When you build a formula in Google Sheets, such as =SUM(A1:A10), the reference A1:A10 is hardcoded. If you want to sum a different column, you must physically click into the formula and retype it.
The INDIRECT function changes this completely. INDIRECT takes a text string—a word you type into a cell—and transforms it into a living, breathing cell reference. This allows you to build incredibly dynamic dashboards where a user can select a sheet name from a dropdown menu, and all the formulas instantly update to pull data from that specific sheet, without altering the formulas themselves.
In this guide, you will learn how to use the INDIRECT function to dynamically reference cells, ranges, and entirely different worksheet tabs.
The Basic INDIRECT Syntax
The INDIRECT function requires only one main argument (the second argument is rarely used and relates to a legacy reference style).
=INDIRECT(cell_reference_as_string)The concept can be confusing at first. Consider this example:
- In cell C1, you type the number
500. - In cell A1, you type the text string
C1. - In cell B1, you type the formula:
=INDIRECT(A1)
Google Sheets evaluates the INDIRECT formula. It looks at cell A1, sees the text “C1”, converts that text into an actual cell reference, and then goes to cell C1 to fetch the number 500. The output in B1 is 500.
Dynamically Referencing Different Sheets
The true power of INDIRECT is creating a master summary dashboard that pulls data from multiple different tabs (sheets) within the same workbook.
Imagine you have three identical tabs named January, February, and March. Cell B10 on every tab contains the total sales for that month. You want a master dashboard where you can simply type the month’s name in cell A1, and cell B1 instantly displays that month’s total.
Standard cross-sheet referencing looks like this: =January!B10
To make this dynamic using INDIRECT, you must use the ampersand (&) operator to concatenate (join) the text in cell A1 with the exclamation mark and cell reference.
In cell B1 on your master dashboard, type:
=INDIRECT(A1 & "!B10")Here is how it works:
- If you type “January” into cell A1, the formula joins “January” with “!B10” to create the text string “January!B10”.
- INDIRECT converts that text string into a real reference, goes to the January tab, and pulls the number from B10.
- If you change A1 to “February”, the formula instantly pulls the data from the February tab.
Important Note: If your sheet names contain spaces (e.g., “Jan Sales”), Google Sheets requires single quotation marks around the sheet name. Your formula must be structured like this: =INDIRECT("'" & A1 & "'!B10")
Creating Dynamic Ranges for SUM and AVERAGE
You can also use INDIRECT to create dynamic ranges inside other functions. For example, you want to SUM a column, but you want to define which row the SUM stops at using a number typed in cell D1.
=SUM(INDIRECT("A1:A" & D1))If you type 50 into cell D1, the formula dynamically becomes =SUM(A1:A50).
The INDIRECT function is a vital tool for advanced spreadsheet design, allowing you to build highly interactive, user-friendly dashboards that adapt instantly without requiring manual formula edits.