INDIRECT for dynamic references in Excel - Dashboard Guide

Dashboard Mode - INDIRECT for dynamic references

Dashboard Goal

See sales totals for different months by selecting the month name. The dashboard updates automatically to show the total sales for the chosen month.

Sample Data

Month	Sales
January	100
February	150
March	200
April	250
May	300
June	350

Dashboard Components

• Dropdown for Month Selection: Cell B9 with data validation list of months (January to June).
• Sales Data Table: Range A2:B7 with months and sales.
• Dynamic Total Sales: Cell B11 shows total sales for the selected month using formula:
  =INDIRECT("B" & MATCH(B9, A2:A7, 0) + 1)
  This formula finds the row of the selected month and returns the sales value in column B for that row.

Dashboard Layout

+----------------------+------------------+
| Month Selection (B9) | Total Sales (B11) |
+----------------------+------------------+
|                      |                  |
|  Sales Data Table     |                  |
|  (A2:B7)             |                  |
+----------------------+------------------+

Interactivity

User selects a month from the dropdown in cell B9. The formula in B11 uses INDIRECT combined with MATCH to find the sales value for that month dynamically. When the month changes, the total sales value updates automatically.

Self Check

If you change the month selection in B9 to "April", what value appears in B11? (Answer: 250)

Key Result

Dashboard shows total sales for a selected month using INDIRECT for dynamic cell reference.