Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Speed up your file

FastExcel
The best tool to optimise your Excel model!
Home > English site > Articles > Show Picture
Deze pagina in het Nederlands

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 and up 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.

Excel 2010

In Excel 2010, the Object button does not work, you have to either use the camera tool, or insert a picture from file (ane pciture file will do), after which you can change the formula.

After inserting the picture or object, 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.

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

Making the picture dynamic

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

Now you can select from the available picture descriptions:

And we're done!

Download

Click here to download a sample file.

Other solutions

Bernie Deitrick came up with this nice sheet, which handles more pictures, but uses VBA


Comments

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

 


Comment by: Daniel Wilson (3/6/2013 4:03:45 PM)

Thanks for your description of how to show a picture from a list of images. It works superbly - we will use it to show small maps of education districts in South Africa in a tool we have developed for the Department of Education. You have helped the cause of education in SA, so well done.

Excellent to find this kind of useful help. Keep it up.

Dan Wilson

 


Comment by: Susan Barbour (4/18/2013 3:59:48 PM)

Hi Jan,
The process works fine for Excel 2010/2013 with one exception - I had the same error as Dave. It's an issue with inserting the bitmap and changing the formula, but I have no idea why! I had used your named range on a previous worksheet and copied that image (the one you rename "=Picture") to my new worksheet and had no problems, but like Dave, no matter how many times I tried to create a new object, I kept getting the same error and it locks up. Thanks for the solution though, once you get past the image part, things are great.

 


Comment by: Jan Karel Pieterse (4/20/2013 9:16:49 PM)

Hi Susan,

Indeed it does not appear to work in 2010. What does work is inserting a picture from file (any valid picture file will do) and then setting the formula.

 


Comment by: Arjo (1/5/2014 11:02:29 PM)

This is great! i've tried it (excel 2007) and it works perfect for my world cup 2014 sheet.

However, The image shown in the object, is resized... Is there a way I can keep it the original size?

and is it possible to use a partly relative cell reference in the 'match' / 'vergelijken' part of the formula?

I hoped I could create it 1 time and after that, just copy the image (because i'd like to have this about 20 times).

 


Comment by: Jan Karel Pieterse (1/6/2014 7:05:16 AM)

Hi Arjo,

Unfortunately, the method described requires a range name for each individual picture. Perhaps the "Other Solution" I mention (See just above first comment) is a better option for your case?

 


Comment by: dean (3/7/2014 10:23:26 AM)

Hi Vinod.

Feel free to contact me on:
dean.justlikethat@gmail.com

I have the solution to your problem

Thanks
Dean

 


Comment by: Djalal Benfadel (7/21/2014 9:38:31 AM)

i have tried you method and other ones too but every time i select the picture and try to write down the defined name in the formula bar a box appears saying the reference is not valid knowing i am using office 2013    

 


Comment by: Jan Karel Pieterse (7/21/2014 8:58:18 PM)

Hi Djalal,

The best way to check is by opening Name Manager and selecting the name in question. Then click in the formula box of Name Manager. If the name is correctly defined, the relevant area in Excel should be highlighted. If nothing is highlighted you have not defined the name properly.

 


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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].