Excel formulas are delayed updating totals
I’ve been meaning to redo the original post with an example that uses tables, because it simplifies things a bit.This is that post — 100% plagiarized from the original when it makes sense to do so. However, it should work fine on Excel 2007 for Windows, too.And, in this example, let’s say we’ve got three different metrics that we’re updating: Revenue, Orders, and Web Visits.This approach can be scaled to include dozens of metrics, but three should illustrate the point.I like to just have the first worksheet as the presentation layer — let’s name it Dashboard — and the second worksheets as the data layer — let’s call that Data.(Note: I abhor many, many things about Excel’s default settings, but, to keep the example as familiar as possible, I’m going to leave those alone.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.
While we’re on this tab, we should go ahead and defined some named cells and some named ranges.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.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.