Conditional Graphic in Excel..


First, you’ll need to have one cell that will use an If-statement to display the word related to your picture. In this particular case, we’ll say that cell A1 of the sheet you want to display the picture in – say Sheet1 – will use your IF-formula to display one of these words: “Apple”, “Pear”, or “Coconut”. For simplicity’s sake, let’s use this formula in cell A1 (referencing the values in cell A2):

=IF(A2=1,”Apple”,IF(A2=2,”Pear”,IF(A2=3,”Coconut”,””)))

So if cell A2 has a value of 1, then cell A1 will say “Apple” and so forth.

Next, select (or add) a blank sheet in your workbook, which we’ll say is Sheet2.

With your blank sheet (Sheet2) selected, go to Tools -> Options -> select the View tab (at the top) -> from the options, de-select (uncheck) the box for Gridlines -> and then click on the “OK” button, so that none of your Gridlines on this blank sheet show.

Now, go to Tools -> Customize -> select the Commands tab (at the top) -> select “Tools” from the left-hand box, then scroll down about 2/3rds the way in the right-hand box until you see a “camera” icon. Left-click and hold down your mouse button on the camera icon; then, drag the icon to your tool-bar (where all your other menu choices are at the top of the Excel program). (NOTE: Before releasing the icon, it should appear as a plus-sign “+”. You can put it either at the top on the right-hand of your menu choices, or in between other tool bars.)

Between cells A1 and C9 of your new sheet (Sheet2), put a picture of a pear. Put a different fruit – an apple – between cells A10 and C18. Finally, put your third picture of a fruit – a coconut or whatever – between cells A19 and C27.

On your new sheet (Sheet2), select cells A1 to C9 by left-clicking and holding in cell A1 and then dragging your mouse until it’s over cell C9 and all the cells in between are highlighted. Now, click on your new camera icon. The area around the picture will show little lines around it. And your mouse-pointer should become a plus-sign.

Now, go to the sheet where you want the picture placed (Sheet1). The plus-sign will still show. Left-click and hold your mouse button down, moving your mouse diagonally so that the outline of the picture is sized and in the place where you want it. When you release the mouse button, the first picture will appear in that spot. (You can click on this picture again to re-size it; and it is a dynamic picture, in that if you put a different picture in the newer sheet, it will be reflected here.)

Now, go to your menu at the top and select Insert -> Name -> Define. In the top box, type in “FruitName” (without quotes). In the “Refers To” box below, type in a formula that references cell A1 on your current sheet and the three different areas of your sheet that holds your pictures. Here’s an example, using what was previously given:

=IF(Sheet1!$A$1=”Pear”,Sheet2!$A$1:$C$9,IF(Sheet1!$A$1=”Apple”,Sheet2!$A$10:$C$18,IF(Sheet1!$A$1=”Coconut”,Sheet2!$A$19:$C$27,””)))

When you’re done with this, click on the “OK” button. (Note: Be sure to use the absolute references here, designated by the dollar-sign, and then to go back and check it afterwards to make sure it didn’t change, as it initially changed to an entire-sheet reference in my initial trial since I didn’t use absolute references.)

Now, click once on the “graphic” image that you’ve previously placed on your sheet (in Sheet1). If you look in the formula bar, it will say something like this:

=Sheet2!$A$1:$C$9

Change that formula to this:

=FruitName

Now, if cell A1 (of Sheet1) says “Pear” (without quotes), a picture of a Pear will be wherever you placed the “graphic” in Sheet1; and it will change to the other graphics also dependent on the text. (Or if the formula is kept intact in cell A1, it will change based on the number 1, 2, or 3 in cell A2.)

You’re just about done: just a couple more things.

Select the sheet where the three graphics are kept (Sheet2). From the menu at the top, select Format -> Sheet -> and then Hide. This will prevent anyone from messing up your graphics, with the originals needing to be retained in their set spots.

You can also select Protection on Sheet1, and it will have no effect on your graphic changing, based on the cell’s value (though you may want to unprotect certain cells if need be).

p.s. I’d recommend that you change your bitmap-image to a JPEG (or GIF), also, as it will take considerably less file-size to include a JPEG.