INDIRECT for dynamic references in Google Sheets - Dashboard Guide

Dashboard Mode - INDIRECT for dynamic references

Dashboard Goal

Help a sales manager quickly see total sales for any chosen month by dynamically changing the data reference.

Sample Data

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

Dashboard Components

• Dropdown Selector (Cell B9): User selects a month from the list January, February, March, April, May, June.
• Total Sales Display (Cell B10): Shows sales for the selected month using the formula:
  =INDIRECT("B" & MATCH(B9, A2:A7, 0) + 1)
  This finds the row number of the selected month and returns the sales value from column B.
• Sales Table (A2:B7): Shows all months and their sales for reference.

Dashboard Layout

+----------------------+-----------------+
|      Sales Table      | Dropdown Selector|
|    (A2:B7)           |     (B9)        |
|                      |                 |
+----------------------+-----------------+
|      Total Sales Display (B10)           |
+------------------------------------------+

Interactivity

When the user picks a month from the dropdown in cell B9, the INDIRECT formula in B10 updates to show that month's sales. This happens because the formula dynamically finds the row of the selected month and returns the sales value from column B.

Self Check

If you change the dropdown in B9 to "April", what value appears in B10?

Answer: 250

Key Result

Dynamic sales total for selected month using INDIRECT formula in Google Sheets.