Reading and writing cell values in Google Sheets - Dashboard Guide

Dashboard Mode - Reading and writing cell values

Dashboard Goal

Track monthly sales and update sales targets easily to see progress.

Sample Data

Month	Sales	Target
January	1200	1500
February	1800	1500
March	1600	1500
April	2000	1500
May	1700	1500

Dashboard Components

• Total Sales: Cell D2 with formula =SUM(B2:B6) shows total sales for all months.
• Average Sales: Cell D3 with formula =AVERAGE(B2:B6) shows average monthly sales.
• Sales Target: Cell D4 with formula =C2 reads January target value to demonstrate reading a cell value.
• Update Target: Cell C7 where user can write a new target value to update all monthly targets using formula =IF(ISBLANK($C$7),1500,$C$7) in cells C2:C6.
• Target Update Formula: Cells C2:C6 have formula =IF(ISBLANK($C$7),1500,$C$7) to read the new target value from C7 or use default 1500.

Dashboard Layout

+----------------------+------------------+
|      Sales Data      |   Summary Cards  |
| +------------------+ | +--------------+ |
| | Month | Sales   | | | Total Sales  | |
| | Target|         | | | =SUM(B2:B6)  | |
| +------------------+ | +--------------+ |
|                      | +--------------+ |
|                      | | Average Sales| |
|                      | | =AVERAGE(B2:B6)| |
|                      | +--------------+ |
|                      | +--------------+ |
|                      | | Current Target| |
|                      | | =C2 (Jan Target)| |
|                      | +--------------+ |
+----------------------+------------------+
+------------------------------------------+
| Update Target: [ C7 ] (User input cell)  |
+------------------------------------------+

Interactivity

User changes the value in cell C7 to update the sales target for all months. The formula in C2:C6 reads this new value and updates the target cells automatically. Summary cards recalculate based on sales data but targets update dynamically based on user input.

Self Check

If you enter 1800 in cell C7, what will be the new target value shown in cell D4 and cells C2:C6? How does this affect the dashboard?

Key Result

Dashboard shows monthly sales, total and average sales, and lets user update sales targets dynamically.