Analyst updating references dating sider for gifte Herlev
The sheet should now look like this: Because this is now a table, as you add data in additional rows, as long as they are on the rows immediately below the table, the table will automatically expand (and that new data will be included in references to Main_Data, which is critical to this whole exercise).
While we’re on this tab, we should go ahead and defined some named cells and some named ranges.
Click in cell C1, select Data » Data Validation, choose List, and then reference the first column in the Main_Data table (see the “Referencing Tables and Parts of Tables” section in this post for an explanation of the specific syntax used here, including the use of the INDIRECT function): When you click OK, you will have a dropdown in cell C1 that contains all of the available months.
This is a critical cell — it’s what we’ll use to select the date we want to key off of for reporting, and it’s what we’ll use to look up the data.
This basic approach is one of the core components in the dashboards I work on every day, and it can be applied to a much more robust visualization of data than is represented here.
This is a slightly iterative process that starts with the setup of the Data tab.
That will give us the “Current” value for each metric.So, we need to make it a named cell — Report Period: Now, let’s do a similar operation for the report range — this tells the spreadsheet how many months to include in each chart.Click in cell C3, select Data » Data Validation, choose List, and then enter the different values you want as options (I’ve used 3, 6, 9, and 12 here, but any list of integers will work): And, let’s name cell Report Range: Does this seem like a lot of work?Instead, we’ll use a combination of the MATCH function (which is sort of like VLOOKUP on steroids) and the INDEX function (which is a handy way to grab a range of cells).Pull your hat down and fasten your seatbelt, as this one gets a little scary.
That leaves us with a Data tab that looks like this: Now, turn that range of data into a table by selecting the area from A2 to D19 and choosing Insert » Table.