INDIRECT for dynamic references in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a flexible sales summary report where they can select a month and see total sales for that month without changing formulas manually.

📊 Data: You have monthly sales data for three months (January, February, March) in separate columns for different products.

🎯 Deliverable: Create a summary table that shows total sales for the selected month using the INDIRECT function to dynamically reference the correct column.

Sample Data

Product	January	February	March
Apples	120	135	150
Bananas	80	90	100
Cherries	60	70	75
Dates	50	65	70

1

Step 1: Create a dropdown list for month selection in cell G1 with options: January, February, March.

Select cell G1, then go to Data > Data Validation > Allow: List > Source: January,February,March

Expected Result

Cell G1 shows a dropdown with the three months.

2

Step 2: In cell G3, calculate the total sales for the selected month using INDIRECT to reference the correct column dynamically.

=SUM(INDIRECT("B2:B5"))

Expected Result

This sums January sales only (since B2:B5 is January column).

3

Step 3: Modify the formula in G3 to use the month selected in G1 to dynamically sum the correct column.

=SUM(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN($B$1)+MATCH($G$1,$B$1:$D$1,0)-1,4),"1","")&"2:"&SUBSTITUTE(ADDRESS(1,COLUMN($B$1)+MATCH($G$1,$B$1:$D$1,0)-1,4),"1","")&"5"))

Expected Result

If G1 is 'February', formula sums February sales (C2:C5) and shows 360.

4

Step 4: Test the dropdown by selecting different months in G1 and observe the total sales update automatically in G3.

No formula change; just change G1 value.

Expected Result

Selecting January shows 310, February shows 360, March shows 395 in G3.

Final Result

Month Selected: [January ▼]

Total Sales for January: 310

(Product sales data in columns B to D with headers January, February, March)

When dropdown changes, total sales number updates accordingly.

✓Using INDIRECT allows formulas to change references dynamically based on user input.

✓This method avoids rewriting formulas for each month and makes reports flexible.

✓Dropdown lists combined with INDIRECT create interactive summary reports.

Bonus Challenge

Extend the report to show total sales per product for the selected month using INDIRECT.

Show Hint

Use INDIRECT with row references to get each product's sales for the month selected in G1.