Chart Selector

Long long standing ovation to Pointy Haired Dilbert for the cute little Excel image hack permitting display of a chart selected from a set of charts, and a nod to Lifehacker for passing the tip along. Coincidentally, I’d been itching to roll up my sleeves toward developing something along the same lines the past several weeks, since adding an APM chart to the head tab of my favorite Excel workbook has filled out that screen’s already cluttered real estate, although I’ve dozens of other charts I would like to have at my fingertips, if only I had space to display them all easily. This chart selector does the trick quite nicely, thank you very much.

Since first applying it to my own fave workbook yesterday, I’ve already tweaked the original tip around the edges, the better to serve my own particular needs, but in ways that ought also be as useful for other Excel workbooks. The main issues I was facing being two-fold:

1. Immediately Refreshed Display Without Recalculation – I gave up automatic recalculation for my fave Excel workbook almost from its launch just over a year ago; and it takes several minutes on a fast machine any time I manually recalculate. (Don’t even bother to ask why. Suffice it to say I’ve got information at my fingertips for which I doubt anyone else in the world has easy access, and I want it all too close at hand to trim it down for speeds’ sake.) Anyway, nice as the original graph selector tip is, it won’t immediately refresh a graph change without either recalculation of the entire workbook, or at least recalculation of an intermediate cell (which was the temporary approach I went to for yesterday’s first pass at the trick).
    
2. Handling a Growing Set of Charts – The only reason I had reached a plateau on graph creation was, as noted, limited front-screen real estate (although occasionally I’ve created ad hoc charts for various aftermath posts). The =IF structure of the original chart selector tip makes the addition of new charts to the selector set more laborious than necessary.

The revisions I’ve made to the trick deal with both these issues in a tight, elegant little formula. I place the names of my charts in column A of my workbook’s “Charts” tab, starting in row 1, naming that vector of chart names ChartList, running down to the final chart. The charts themselves are in column B of the “Charts” tab, each in cells sized as directed in the original chart selector tip. Cell AD1 on “LBR” – the header tab of my favorite Excel workbook – contains a dropdown list pulling in the names from ChartList; while cell AD2 of the same tab contains the chart-carrier picture with the hack described in the original tip. (Yes, that’s not a typo – for reasons irrelevant to this discussion, column AD is at about the center of the northwestmost screen of the header tab for my fave Excel spreadsheet.)

In lieu of the original tip’s =IF formula, I now use the following formula –

   =INDIRECT("'Charts'!B"&TEXT(MATCH(LBR!$AD$1,ChartList,0),"#"))

Now anytime I select a different chart name from the dropdown list on my fave Excel workbook’s header tab, the chart is instantly refreshed to the new selection, without requiring recalculation of so much as a single cell of the rest of the workbook. Meanwhile, anytime I wish to add a new chart, I simply insert a row within the ChartList range on the Charts tab, add the name of the chart in column A, slide the chart into column B (usually simply by copying one of the other charts, then revising the source data ranges), and presto, my chart selector is automatically updated, without having to go through the formula revision that would be necessary under the original tip.