Bird
Raised Fist0
Google Sheetsspreadsheet~15 mins

INDIRECT for dynamic references in Google Sheets - Real Business Scenario

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Scenario Mode
👤 Your Role: You are a sales analyst at a retail company.
📋 Request: Your manager wants a summary sheet where they can select a month and see total sales for that month without changing formulas manually.
📊 Data: You have monthly sales data for January, February, and March in separate sheets named 'Jan', 'Feb', and 'Mar'. Each sheet has two columns: Product and Sales.
🎯 Deliverable: Create a summary sheet with a dropdown to select the month and a formula that shows total sales for the selected month dynamically.
Progress0 / 3 steps
Sample Data
ProductSales
Apples100
Bananas150
Cherries200

Note: This table is the same for each sheet 'Jan', 'Feb', and 'Mar' but with different sales numbers.

ProductSales
Apples120
Bananas130
Cherries180

Note: This is the 'Feb' sheet data.

ProductSales
Apples140
Bananas160
Cherries210

Note: This is the 'Mar' sheet data.
1
Step 1: Create a new sheet named 'Summary'. In cell A1, create a dropdown list with the options 'Jan', 'Feb', and 'Mar' using Data > Data validation.
Select cell A1 in 'Summary' sheet. Go to Data > Data validation. Choose 'List of items' and enter: Jan,Feb,Mar. Click Save.
Expected Result
Cell A1 shows a dropdown with options Jan, Feb, and Mar.
2
Step 2: In cell B1 of the 'Summary' sheet, enter a formula to calculate total sales for the selected month using INDIRECT to refer to the correct sheet dynamically.
=SUM(INDIRECT(A1 & "!B2:B4"))
Expected Result
If 'Jan' is selected in A1, B1 shows 450 (100+150+200). If 'Feb' is selected, B1 shows 430 (120+130+180). If 'Mar' is selected, B1 shows 510 (140+160+210).
3
Step 3: Test the dropdown by selecting each month and observe the total sales update automatically in cell B1.
No formula needed; just change dropdown in A1.
Expected Result
Total sales in B1 update correctly for each month selected.
Final Result
Summary Sheet

+-------+-------+
| Month | Total |
+-------+-------+
| Jan   |  450  |
| Feb   |  430  |
| Mar   |  510  |
+-------+-------+

User selects month in cell A1 dropdown, total sales appear in B1.
✓Using INDIRECT allows dynamic reference to different sheets based on user selection.
✓This method avoids rewriting formulas for each month.
✓The summary updates instantly when the month changes.
Bonus Challenge

Modify the summary sheet to also show total sales for a selected product across all months using INDIRECT and dropdowns.

Show Hint
Create a product dropdown and use SUM with INDIRECT to sum sales for that product from each month sheet.

Practice

(1/5)
1. What does the INDIRECT function do in Google Sheets?
easy
A. It finds the maximum value in a range.
B. It sums all numbers in a range.
C. It converts a text string into a cell or range reference.
D. It sorts data alphabetically.

Solution

  1. Step 1: Understand the purpose of INDIRECT

    The INDIRECT function takes a text string and treats it as a reference to a cell or range.

  2. Step 2: Compare with other options

    Options B, C, and D describe other functions like SUM, MAX, and SORT, not INDIRECT.

  3. Final Answer:

    It converts a text string into a cell or range reference. -> Option C

  4. Quick Check:

    INDIRECT = converts text to reference [OK]
Hint: Remember: INDIRECT turns text into a live cell reference [OK]
Common Mistakes:
  • Confusing INDIRECT with SUM or MAX functions
  • Thinking INDIRECT changes cell values
  • Assuming INDIRECT sorts data
2. Which of these is the correct syntax for the INDIRECT function in Google Sheets?
easy
A. INDIRECT(reference_text, [a1])
B. INDIRECT(reference_text; [a1])
C. INDIRECT(reference_text a1)
D. INDIRECT(reference_text, a1, extra)

Solution

  1. Step 1: Recall the syntax format

    The correct syntax uses a comma to separate arguments: INDIRECT(reference_text, [a1]) where a1 is optional.

  2. Step 2: Check other options for errors

    INDIRECT(reference_text; [a1]) uses a semicolon which is incorrect in Google Sheets formulas. INDIRECT(reference_text a1) misses commas and brackets. INDIRECT(reference_text, a1, extra) has an extra argument which is invalid.

  3. Final Answer:

    INDIRECT(reference_text, [a1]) -> Option A

  4. Quick Check:

    Correct syntax uses comma and optional second argument [OK]
Hint: Use comma between arguments; second argument is optional [OK]
Common Mistakes:
  • Using semicolons instead of commas
  • Adding extra arguments
  • Omitting commas between arguments
3. Given cell A1 contains the text "B2" and cell B2 contains the number 50, what will the formula =INDIRECT(A1) return?
medium
A. The text "B2"
B. The number 50
C. An error because B2 is text
D. Blank cell

Solution

  1. Step 1: Understand the INDIRECT reference

    The formula INDIRECT(A1) reads the text in A1, which is "B2", and treats it as a reference to cell B2.

  2. Step 2: Check the value in referenced cell

    Cell B2 contains the number 50, so INDIRECT(A1) returns 50.

  3. Final Answer:

    The number 50 -> Option B

  4. Quick Check:

    INDIRECT("B2") = value in B2 = 50 [OK]
Hint: INDIRECT uses text as address, returns that cell's value [OK]
Common Mistakes:
  • Thinking INDIRECT returns the text itself
  • Expecting an error if referenced cell has a number
  • Confusing cell content with reference
4. You wrote the formula =INDIRECT(A1:B1) but it returns an error. What is the likely problem?
medium
A. INDIRECT only works with numbers, not text.
B. You forgot to add quotes around A1:B1.
C. The formula needs a third argument to work.
D. INDIRECT cannot take a range as reference_text, only a single cell text.

Solution

  1. Step 1: Analyze the argument type

    The INDIRECT function expects a single text string as reference_text, not a range like A1:B1.

  2. Step 2: Understand why the error occurs

    Passing a range A1:B1 as reference_text causes an error because INDIRECT cannot process multiple cells as text input.

  3. Final Answer:

    INDIRECT cannot take a range as reference_text, only a single cell text. -> Option D

  4. Quick Check:

    INDIRECT needs single text reference, not range [OK]
Hint: INDIRECT needs one text cell, not multiple cells [OK]
Common Mistakes:
  • Passing a range instead of a single cell text
  • Adding unnecessary extra arguments
  • Thinking INDIRECT works only with numbers
5. You want to sum values in different sheets dynamically. Cell A1 contains the sheet name (e.g., "Sales2023"). Which formula correctly sums range B1:B10 in the sheet named in A1?
hard
A. =SUM(INDIRECT(A1 & "!B1:B10"))
B. =SUM(INDIRECT("A1!B1:B10"))
C. =SUM(INDIRECT(B1:B10 & "!" & A1))
D. =SUM(INDIRECT("B1:B10"))

Solution

  1. Step 1: Build the dynamic reference string

    Concatenate the sheet name in A1 with "!B1:B10" to create a text like "Sales2023!B1:B10".

  2. Step 2: Use INDIRECT to convert text to reference

    INDIRECT(A1 & "!B1:B10") converts the text to a range reference on the specified sheet.

  3. Step 3: Sum the values in that range

    SUM(...) adds all numbers in the referenced range.

  4. Final Answer:

    =SUM(INDIRECT(A1 & "!B1:B10")) -> Option A

  5. Quick Check:

    Concatenate sheet name + range, then INDIRECT + SUM [OK]
Hint: Join sheet name + "!range" inside INDIRECT for dynamic refs [OK]
Common Mistakes:
  • Putting cell references inside quotes incorrectly
  • Swapping sheet name and range order
  • Not concatenating sheet name and range properly
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard