Visual modelling

If you've ever tried to show a number of charts and tables in one spot on the screen, making all except one of them invisible when the user makes a selection, you'll know how messy it can get. You can also get problems if you want some tables to have different width columns without affecting other tables.

This model has an elegant solution. There is not a single chart or table on the results sheet. Instead, Excel takes a "picture" of the chart or table you want to show (and the chart/table can be on any sheet in the workbook), and shows that picture.

This means you can build all the tables and charts you need, anywhere you like except on the presentation sheet. All you have to do is tell Excel which range of cells to use for its picture.

This has a number of benefits

Best of all, this requires no code whatsoever. That's right, no VBA.

The example here demonstrates.

What it does  

Excel has a "hidden" feature which applies to pictures - yes, clipart or any other images you import. Instead of a normal image, you can configure a picture to act as a "window" onto a range of cells in your workbook, even on another sheet. Whatever is in those cells (including a chart, if it covers those cells) will appear in the picture.

So you can set up a picture to show (say) a chart which is actually on another sheet. Then, you can use a dynamic rangename (see the example workbook for details) to make the picture point to different places in the workbook, depending on what the user selects.

I rate this as one of the most powerful features I've ever seen in Excel.

 

Note: Where possible, I've attributed ideas to the people who thought of them. If I have missed someone or made a mistake, please let me know.