Areas and iterations not updating
Start by setting up some labels with dropdowns (I normally put these off to the side and outside the print range…but that doesn’t sit nice with the screen resolution I like to work with on this blog): Then, set up the dropdowns using Excel data validation: First, the report period.
See for a look at my thoughts on dashboard visualization.) This is a slightly iterative process that starts with the setup of the Data tab.
Define a new range with a Workbook scope that encompasses all the columns and all of the rows of data (starting at row 3): There are lots of ways to dynamically define Main Data.
You can just drag a big area if you want, but this is a slightly more elegant approach.
Let’s leave the first row alone — this is where we will populate the “current value,” which we’ll get to later.
I like to use a simple shading schema to clearly denote which cells will get updated with data and which ones never really need to be touched.