Lunchtime learning - Excel
to lunchtime menu
Do make sure that any tips and suggestions work as you expect them to in your own particular circumstances.
Search other Office resources (AccountingWeb, Beancounters Guide, IT Counts):
Excel camera - choosing multiple areas
Some time ago we looked at how the Excel camera can be used to assemble areas from different sheets of a workbook into a single printing area: using the Excel camera to assemble a report. This time we will go further and show how to base a set of camera 'pictures' on the choice in a single list box. The technique depends on the following elements and techniques:
First we will create three camera pictures and use Data Validation to create a list box with the options 1, 2 and 3.
We will now define a new Range Name called CondCam1 and, rather than referring to a cell, we will refer directly to a formula:
=CHOOSE(ConditionalCamera!$B$1,'Financial accounts'!$I$3:$I$23,'Financial accounts'!$J$3:$J$23,'Financial accounts'!$K$3:$K$23)
Our Data Validation list is set up in cell B1 of the 'ConditionalCamera' sheet and the reference to this cell is the first argument of our CHOOSE() function. This returns number 1, 2 or 3 which is used as the 'index' to which of the next 3 arguments will be used. We enter those three arguments as references to different cell ranges on the sheet 'Financial accounts'.
If we click on one of our pictures we can see the original formula in the formula bar:
We can just replace the reference to the range with a reference to our Range Name:
We can then repeat the process for the other two camera pictures using the Range Names CondCam2 and CondCam3. Now, when we choose the item from our list box, each of our range names will choose the corresponding block of cells:
|© 1998 The Knowledge Base. Disclaimer Contact firstname.lastname@example.org
Design by Reading Room Ltd 1998