How to Use the Google Sheets INDIRECT Function to Reference Variable Cells

When building a formula in Google Sheets, you typically hard-code the cell references. For example, =SUM(A1:A10) tells the spreadsheet exactly where to look. However, when building complex, interactive dashboards, you often need the formula to change its target dynamically based on what a user types or selects from a drop-down menu. To achieve this, you cannot use standard references. You must use the INDIRECT function, a powerful tool that transforms plain text strings into active, calculable cell addresses.

What Does the INDIRECT Function Do?

The INDIRECT function takes a string of text (e.g., the word “B5”) and forces Google Sheets to treat it as a literal coordinate on the grid. This allows you to construct cell references mathematically or pull the coordinates from other cells, enabling highly dynamic spreadsheets that update their entire logic structure without you having to rewrite the formulas.

Understanding the Syntax

The syntax for the formula is simple, requiring one mandatory argument:

=INDIRECT(cell_reference_as_string, [is_A1_notation])

• cell_reference_as_string: The text string representing the address.
• [is_A1_notation] (Optional): A boolean (TRUE/FALSE) indicating if you are using standard A1 coordinates or legacy R1C1 coordinates. It defaults to TRUE.

Basic String Transformation

To understand the mechanics, consider a simple translation.

1. Open your Google Sheets document.
2. In cell A1, type the word: Apple.
3. In cell B1, type the text string: A1.
4. Click into cell C1 and type the formula:

=INDIRECT(B1)

5. Press Enter.

Cell C1 will output the word Apple. The formula did not look at the value of B1; it looked at the text inside B1, recognized it as a coordinate, and then traveled to that coordinate to fetch the final data.

Building Dynamic Summary Dashboards

The true value of INDIRECT becomes apparent when querying data across multiple tabs. Imagine you have a workbook with 12 tabs, one for each month (named “Jan”, “Feb”, “Mar”, etc.). On each tab, the total monthly revenue is calculated in cell D20.

On your master dashboard tab, you want to create a drop-down menu where a user can select a month, and the revenue instantly appears next to it.

1. In cell A1 of your dashboard, create a drop-down menu containing the exact tab names (“Jan”, “Feb”, etc.).
2. Assume the user selects “Feb” from the drop-down.
3. In cell B1, you write the following formula:

=INDIRECT("'" & A1 & "'!D20")

Here is what happens: The formula concatenates the text. It takes the apostrophe, adds the text from A1 (“Feb”), and appends the static cell address (“‘!D20”). The final, invisible text string becomes 'Feb'!D20.

The INDIRECT function wraps around this string, converts it into a live reference, jumps to the “Feb” tab, looks at cell D20, and pulls the revenue data back to the dashboard. When the user changes the drop-down menu to “Mar”, the text string changes, the coordinate changes, and the data updates instantly, creating a fully interactive reporting tool.