### Inserting an updating date in excel

Data Tab Setup — Part 1 This is a slightly iterative process that starts with the setup of the Data tab. After creating one of these named ranges, while still in the Name Manager, you can select the range and click into the formula box, and the current range of cells defined by the formula will show up with a blinking dotted line around them. You can just drag a big area if you want, but this is a slightly more elegant approach. Pull your hat down and fasten your seatbelt, as this one gets a little scary. Does this seem like a lot of work? When you click OK, you will have a dropdown in cell C1 that contains all of the available months. There are lots of blogs devoted entirely to Excel tips and tricks. Here are the criteria I was working against: As a matter of fact, this is really just trying to identify the cell location not the value in the cell of the current value for revenue — very similar to what we did with the VLOOKUP function earlier. In practice, we use this technique to populate a slew of sparklines no x-axis labels and a couple of bar charts, as well as some additional calculated values for each metric. Revenue, Orders, and Web Visits. The formula is pretty simple: OR…you can move on to the final step, where it all comes together! All we want this function to return is the number of the row in the MainData array for the currently selected report period, which, as it turns out, is the same row as the currently selected report period in the DataSelector range. Dashboard Tab Setup — Part 1 Now we jump over to the Dashboard worksheet and set up a couple of dropdowns — one is the report period selector, and the other is the report range how many months to include in the chart selector. Before we leave this tab, go ahead and select a value in each dropdown — this will make it easier to check the formulas in the next step. Name each cell by clicking on the cell, then clicking in the cell address at the top left and typing in the cell name. Ultimately, the formula looks like this: We want to look at the currently selected Report Period a named range called ReportPeriod and find the value for each metric that is in the same row as that report period. That post is available here. We do this using the MATCH function, which we need to use on a 1-dimensional array rather than a 2-dimensional array MainData is a 2-dimensional array. Data Tab Setup — Part 2 Now is where the fun begins. 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. It will just be a blank box initially: Repeat this last step to create two more named ranges with slightly different formulas the differences are in bold: Setting Up the Basics One key here is to separate the presentation layer from the data layer. In order for the chart labels to show up correctly, we need to make one more named range.