My favorite on that front is Jon Peltier’s (if you get intrigued by this post, hop over and peruse a slew of other ways to have charts dynamically update).
All you need to do is put the So, the formula simply takes the currently selected month, finds the row with that value in the data array, and then moves over to the column that matches the current column of the formula: Slick, huh?
And, because the Report Period data validation dropdown on the Dashboard worksheet is referencing the on the Data tab, the VLOOKUP will always be able to find a matching value.
It can be a bit of a hassle on the initial setup, but it will pay huge dividends as the report gets updated each day, week, or month. 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. We’re going to go back over to the Data worksheet and start setting up some additional named ranges.
We’ve got Main Data, which is the full range of data.
I’m seeing a lot of referral traffic to this post searching for Excel 2010.
If you’re simply looking for where you define or modify named ranges in Excel 2010 (as one commenter indicated in response to an earlier version of this update), it’s on the Formulas tab in the Defined Names area — Name Manager.For the purposes of this example, we’ll go with months.Let’s leave the first row alone — this is where we will populate the “current value,” which we’ll get to later.We want to look at the currently selected Report Period (a named range called Report Period) and find the value for each metric that is in the same row as that report period.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.