Show a graphic by choice
What this page provides
What this page provides
What this page provides
There are times when you will need to show a graphic on an Excel worksheet, but what if you need to show different graphics depending on choices/results in the worksheet? Then you will need to dynamically show a graphic of choice, depending on whatever choices or results exist in the worksheet, and the method of achieving that is shown on this page.
Add Camera Tool to QAT
What this page provides
What this page provides
First of all, we need to create a graphic object that we will adapt for our purposes. To create a graphic object, one that is linked to the display of a fixed area of a worksheet, use the Camera tool.
The Camera tool is not available in any of the Ribbon menu areas, so we must add that to the Quick Access Toolbar at the top of the screen ... right-click the Quick Access Toolbar (QAT), and choose Customize Quick Access Toolbar from the context menu. This will display a dialog of Excel options for modifying the Quick Access Toolbar ... the Choose Commands From drop-down allows choices of subsets of commands to be listed ... choose All Commands. In the list of all commands, choose Camera (the tools are listed in alphabetical order), and click on the Add button. OK.
Create a graphic object
What this page provides
Name the areas for display
To use the Camera tool, first select one of the areas you want displayed in a graphic.
Then click on the Camera tool icon, and the move the mouse to an area of the worksheet away from the selected area, and left-mouse-click where you want the graphic to be (you can drag/cut it elsewhere later).
Name the areas for display
Point the graphic to the formula
Name the areas for display
Select one of the areas to be displayed (it may even be the one we already used as the source for the Camera tool action), and create a defined name for it ... use the Ribbon command Formulas/Define Name. Repeat, creating different names for each area that may be displayed.
Create a formula in a name
Point the graphic to the formula
Point the graphic to the formula
We also need to create a new defined name of a formula. Define a name of, say, Graphic_Choice, which refers to the conditions on the worksheet to be tested for a decision on which graphic to display.
For example, if we have 2 cells in Sheet2, one for a target score (B3), and another for score achieved (B5), we might use this formula:
=INDIRECT(IF(Sheet2!$B$5>=Sheet2!$B$3,"Success","Almost"))
This will generate a reference to either the named area called Success or the named area called Almost.
Point the graphic to the formula
Point the graphic to the formula
Point the graphic to the formula
Select the graphic object that was created by the Camera tool ... you will see in the Formula bar the reference of the area that the graphic area shows. We need to over-type that reference with our newly created defined name of Graphic_Choice.
Example
Example
Example
Move the graphic to the sheet and area where you want it to be, and see it change as the contents of the sheet change. An example is available to download here.