Month tab picker
=INDIRECT(A1&"!C10") reads cell C10 on whichever month name sits in A1.
What is INDIRECT in Google Sheets?
INDIRECT evaluates a text string as a cell reference. Build addresses from dropdown selections, month tab names, or column letters generated by formulas. It powers dynamic dashboards but is volatile and easy to break with typos.
When to use it
Use INDIRECT when the target sheet name or column comes from a cell your users pick, such as a month selector pulling from Jan, Feb, or Mar tabs.
When to skip it
Skip INDIRECT when a simple static reference or INDEX with row/column numbers is enough. INDIRECT recalculates often and confuses auditors.
How it works
-
1
Build a reference string like "Summary!B5" or B$1 & "!A1" in helper cells.
-
2
Wrap the string in INDIRECT to dereference it.
-
3
Use INDIRECT with ADDRESS for row-column numeric construction.
-
4
Quote sheet names with spaces inside the string carefully.
-
5
Pair with named ranges when possible to reduce fragile strings.
-
6
Document allowed tab names so INDIRECT strings stay valid.
Examples in Google Sheets
Column letter build
=INDIRECT("Prices!"&B1&"2") uses a letter in B1 to pick a price column row 2.
From ADDRESS
=INDIRECT(ADDRESS(5, 3)) points at C5 without typing C5 directly.
Build this without starting from a blank cell
Use a Better Sheets tool for INDIRECT, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Missing quotes around sheet names with spaces in the INDIRECT string.
- Typo in tab name returning #REF! across the whole dashboard.
- Overusing INDIRECT slowing recalc on large workbooks.
- Building references that point at deleted tabs after renames.
- Expecting INDIRECT to work with external workbook paths like Excel.
Frequently asked questions
- INDIRECT vs INDEX?
- INDEX takes numeric positions. INDIRECT parses text addresses.
- INDIRECT another sheet?
- Yes. Include SheetName! in the text string.
- Why #REF! on INDIRECT?
- Invalid address string, deleted sheet, or out-of-range cell.
- INDIRECT volatile?
- Yes. It recalculates often and can slow big models.
- INDIRECT with named ranges?
- You can indirect to a name if the name exists.
- INDIRECT array?
- Some array forms exist but are advanced. Test spill behavior.
- Safer alternative?
- CHOOSE, SWITCH, or IFS on static references avoid INDIRECT fragility.
Related Tutorials
Watch how INDIRECT works
How do I reference a different spreadsheet in Apps Script?
Related blog posts
Guides that explain INDIRECT in more depth.
5 Advanced Formulas To Build a Simple Monthly Budget Template
Track your expenses, manage your finances, hoping to save money, plan for the future, and generally during the month you want to reduce stress. Use these advanced formulas to get more out of Google Sheets.
Read post →How to Gain Confidence in Google Sheets
Boost your confidence, master formula usage and overcome errors in this step-by-step guide. Join us to demystify spreadsheets.
Read post →The Most Popular Google Sheet Tutorial on the IF() Formula
The IF() formula is a logical function that makes it easy for users to compare a certain value and an expected value. It takes a look at a value to see if basic conditions are met.
Read post →Related glossary terms
OFFSET
OFFSET starts from a reference cell and returns a range moved by a row and column offset with optional height and width. Rolling averages, dynamic windows, and shifted blocks use OFFSET, though INDEX and FILTER are often clearer today.
Read guide →INDEX
INDEX fetches a value from a range by row and column number. Combined with MATCH it powers flexible lookups. Alone it picks nth items from lists, dynamic headers, or matrix intersections without VLOOKUP column limits.
Read guide →MATCH
MATCH searches a range for a value and returns its relative position. Feeding INDEX or OFFSET with MATCH replaces brittle column index math. Sorted approximate match modes support tier tables and date brackets.
Read guide →VLOOKUP
VLOOKUP searches the first column of a range for a key and returns a value from a specified column to the right. It is still everywhere in legacy templates even as XLOOKUP offers more flexible replacements.
Read guide →Done reading about INDIRECT?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.