Showing a picture from a list of pictures

Introduction

Many people keep part lists in Excel. Sometimes even with embedded pictures next to the part data.

This article demonstrates a method to show a picture on a sheet that changes when a different part number is chosen from the list of available part numbers.

Preparing the list

The first thing to do is to position the pictures properly. Each picture should go in a cell on the same row as a unique description (part number) of the part it pertains to.

Format the cells, so the picture fits in a single cell (either adjust the size of the picture, or adjust row height and column width):

Figure 1, screenshot showing a list of icons next to their description

Preparing the sheet that shows the dynamic picture

We need some way of knowing which picture is to be shown. We start off by defining a dynamic range name that will expand automatically when we add new items to our list (assuming they are on Sheet1, starting on cell A1, with one row of headings) Choose Insert, name define (In Excel 2007 on the "Formulas" tab of the ribbon, in the "Defined Names" group, click the "Define Name" button) and define this name:

Name	Refers to
PictureList	=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1)

Let us say cell A2 on sheet2 will be used to enter the picture code we want to see the picture from. The formula in the next defined name will point to the cell to the right of the cell who's picture name matches the text entered in cell Sheet2!A2:

Name	Refers to
Picture	=OFFSET(Sheet1!$B$2,MATCH(Sheet2!$A$2,PictureList,0)-1,0,1,1)

Creating the picture

Excel 2007

There are two methods to insert the proper picture in Excel 2007. One is by adding the camera tool to your QAT and using that, the other is shown below.

Select the cell where you want your dynamic picture to appear and click the Insert tab of the ribbon. On the far right, find the "Object" button and click it (see screenshot below).

Figure 2, Insert Object button on the ribbon

The Insert object dialog appears:

Figure 3, Object Dialog

Select the top option (Bitmap Image) and click OK. Hit escape to return to Excel. The newly inserted picture object should still be selected. The formula bar should show something like =EMBED("Paint.Picture";""). Click in the formula bar and replace the entire formula with this formula: =Picture

Figure 4, screenshot showing the picture's formula bar.

Hit enter or click the green checkmark to the left of the formula bar.

Excel 97 to 2003

Select the cell with the topmost picture. Hold down the shift key and click the Edit menu. It should show "Copy Picture...":

Figure 4, screenshot showing part of Excel's Edit menu when clicked whilst holding down the shift key.

Select "As shown on screen" from the next dialog and click OK.

Now navigate to the cell (on Sheet2 in this example) where you want to show the dynamic picture and hit the paste button (or control-v):

Figure 5, screenshot showing the pasted picture.

This picture -of course- is static. To make it dynamic, we can use a formula as the source of the picture.
Click on the picture so it is selected. Then click once in the formula bar:

Figure 6, screenshot showing the pasted picture's formula bar.

Type the equal sign, followed by the name "Picture", as shown above and hit the enter key.
Now select cell A2 and type one of the descriptions from the list into that cell. The picture belonging to that description should appear.

Making the picture dynamic

To simplify the selection of the picture description, we can use Data, validation:

Select cell A2 and choose Data, Validation... from the menu.
From the top dropdown, select the List option
In the Source box enter this formula: =PictureList
If needed, fill in the fields on the other tabs too ("Input message" and "Error Alert")
Click OK:

Now you can select from the available picture descriptions:

And we're done!

Download

Click here to download a sample file.

Comments

Showing last 8 comments of 53 in total (Show All Comments):

Comment by: Salahuddin (4/20/2010 10:37:04 AM)

Hi, i m trying ur example but can't do it.could u send me an example by using a picture in Excel 2003. Specialy i m not understood in preparing the list. The symble of pictures come from where?

Comment by: Jan Karel Pieterse (4/20/2010 10:11:19 PM)

Hi Salahuddin,

The article comes with an example file to download. See the section called "Download".

Comment by: Sam (6/15/2010 10:21:46 AM)

Hi, your site helped me alot, but I have a problem when I print the excel sheet. The printed image shows 3 of the images I have on my picturelist when its supposed to only have one of the picture om my picturelist. The difference between the one that I made and yours is that my pictures are bigger. And sometimes the image doesn't even appear when I print. Please help.

Comment by: Jan Karel Pieterse (6/16/2010 12:31:11 AM)

Hi Sam,

You can send me the file, I'll have a quick look free of charge.

Comment by: Allison (6/16/2010 7:50:04 AM)

Hi,
I used this method to create a worksheet with dynamic pictures in Excel 2003 and it worked perfectly. I recently had Excel 2007 installed, and the dynamic pictures still work great. However, I am not having a problem with the dynamic picture order (which was not a problem in Excel 2003). I cannot get the picture "order" to "send to back". It is always in the front, and I cannot layer stuff on top of it. Do you know why this is and if there is a way to work around this? I have tried lots of things.. and can't seem to find a solution.

Thanks for your help,
Allison

Comment by: HROGO (6/25/2010 1:48:44 PM)

brilliant!!! I like the way you solved the problem, simple yet effective.

Comment by: Louise (7/8/2010 11:18:25 AM)

Many thanks - just what I needed and an easy solution(but only easy because of your very clear instructions).

Comment by: Jan Karel Pieterse (7/8/2010 11:18:50 AM)

Hi Louise,

You're welcome!

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.