INDIRECT for dynamic references in Google Sheets - Real Business Scenario

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.

Sample Data

Product	Sales
Apples	100
Bananas	150
Cherries	200

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

Product	Sales
Apples	120
Bananas	130
Cherries	180

Note: This is the 'Feb' sheet data.

Product	Sales
Apples	140
Bananas	160
Cherries	210

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.