Most Valuable Professional


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

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
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

All comments about this page:


Comment by: viswanath (11/18/2008 5:27:24 AM)

ok it is fine but we are traid it can't show the picture
we written in the source as a picture list it shows the source path cannot be found pl give ur suggestion

Regards
Viswanath.

 


Comment by: Jan Karel Pieterse (11/18/2008 7:46:03 AM)

Hi viswanath,

Not sure what you are trying to accomplish, could you elaborate?

 


Comment by: viswanath (11/18/2008 8:06:28 PM)

Sir,
     We have a project in that total employee details can available once we select the emp.code its display total details of perticular emp.with photo. So that we used the above farmula in that source (picture list) can't taken and it shows "A named range you specified cannot be found" that's why we taken Ur file and completed the task but we need how to display the photo in required coloum. pl do the needful

With regards,
Viswanath.

 


Comment by: Jan Karel Pieterse (11/18/2008 10:17:07 PM)

Hi Viswanath,

Please send me your file so I can have a look. My email address is at the bottom of this page.

 


Comment by: vinod kumar (12/17/2008 12:14:59 AM)

Hi Sir,

This is vinod from India.

I need a small info regarding Excel. How to map the hyperlink sheet ( Word or Excel PP) in the cambo list.

Awaiting for your solution.

Vinod

 


Comment by: Jan Karel Pieterse (12/17/2008 2:19:02 AM)

Hi Vinod,

I am not sure I understand what you are asking, could you please explain a bit more?

 


Comment by: vinod (12/17/2008 2:52:01 AM)

Name of the Employee : Ram
suppose if i click the name of the person,Whole History of the particular person will apper in sheet. (history in form Word or Excel Or PP template).

how to link the sheet with name ( Vlookup) or any other method.

 


Comment by: Jan Karel Pieterse (12/17/2008 3:21:31 AM)

Hi Vinod,

You could use the VLOOKUP worksheet function to pull the relevant data from a table in Excel that holds the employee information. Look up VLOOKUP in Excel Help to find out more.

 


Comment by: Dan (12/17/2008 1:05:02 PM)

I'm not sure where the formulas go. I cant find them on your available download, nor do i fully understand from the explanation provided in the "Preparing the sheet that shows the dynamic picture" section of this page. In addition, Creating the image is not working for me. I consider myself fairly experienced with excel and I am having a really hard time wrapping my head around this. Thanks for any support.

Dan (Excel 2007)

 


Comment by: Jan Karel Pieterse (12/18/2008 1:10:07 AM)

Hi Dan,

The formulas go in range names, in Excel 2007 they are available through the Name Manager button on the formulas tab of the ribbon. That is also the way to find them in my sample file.

Regards,

Jan Karel

 


Comment by: Paco (2/7/2009 4:11:51 PM)

Hi Jan:

Great article and it has helped me to confirm a process I was trying ....
A quick question, though: is there any way of getting rid of the "frame" around the picture (i.e., the lines around the picture in cell B2 of Sheet2, in your example)?
The only way I have been able to do this is to fill the sheet with a gray background or by adjusting the brightness).
Any thoghts would be greatly appreciated ....

Regards,
Paco

 


Comment by: Jan Karel Pieterse (2/8/2009 10:47:48 PM)

Hi Paco,

Either you have a border set on the picture:
Rightclick picture, "Format picture", colors and lines tab, set line color to "No Line".

Or you have included the cell border with the picture. Select the sheet from where you took the picture, select Tools, options, view tab and uncheck gridlines.

 


Comment by: Josh (3/21/2009 8:47:39 AM)

Hello Jan,

Could you explain if this can be translated onto a form?

Thanks very much,

Josh

 


Comment by: Jan Karel Pieterse (3/21/2009 11:39:27 AM)

Ho Josh,

No, it can't. On a form you'd use the picture control and load the pictures from files.

 


Comment by: james jones (4/5/2009 5:51:59 AM)

is it possible using this method to have multiple changable images on a sheet dependant on list name?

 


Comment by: Jan Karel Pieterse (4/6/2009 12:46:55 AM)

Yes, but you'll have to create range names for each of them, which is a bit cumbersome.

 


Comment by: Todd G (5/11/2009 6:47:04 PM)

I'm trying to change your code about a bit, displaying the changing images on sheet 1 referring to the list of names and images on sheet 2. When I make a choice from the drop-down list, the image does not update automatically. I need to select the image, and re-enter the =Picture formula from the display. I can't work out where it's going wrong.

My sheets are basically the same, only swapped. Here's my definitions:
Picture: =OFFSET(Sheet2!$B$2,MATCH(Sheet1!$A$2,PictureList,0)-1,0,1,1)

PictureList: =OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,1)

Any suggestions?

 


Comment by: Jan Karel Pieterse (5/12/2009 9:57:07 PM)

Hi Todd,

Maybe Excel doesn't think the range names need to be recalculated. Try this:

PictureList: =OFFSET(Sheet2!$A$1,1,0*NOW(),COUNTA(Sheet2!$A:$A)-1,1)

 


Comment by: Kyle (5/13/2009 7:17:01 PM)

Is it possible to have the Object display nothing when there is no selection in the picture list drop down menu?

 


Comment by: Jan Karel Pieterse (5/13/2009 11:34:58 PM)

Hi Kyle,

I think the simplest method is to have a picture named "Blank" and a Blank choice in the dropdown.

 


Comment by: Scott (7/7/2009 1:47:27 AM)

I followed your directions and code but the only way I can get the image to update once the drop down menu changes selection is to click in the formula bar and hit enter. Any ideas?

 


Comment by: Jan Karel Pieterse (7/7/2009 1:49:03 AM)

Hi Scott,

Is calculation perhaps set to manual?
Maybe the solution I suggested to Todd works:

Change the formula of this name :

PictureList
=OFFSET(Sheet2!$A$1,1,0*NOW(),COUNTA(Sheet2!$A:$A)-1,1)

 


Comment by: Jerome Angles (8/28/2009 9:18:10 AM)

Brilliant page. Still struggling with having no pictures displayed when customer open my form... but getting there.

Thx a mil!!

Jerome

 


Comment by: leila (10/9/2009 12:16:44 AM)

how can load picture to use in form with vlookup excel vba?

 


Comment by: Jan Karel Pieterse (10/9/2009 5:16:39 AM)

Hi Leila,

Not sure what you are asking. Do you want the picture to appear on a VBA Userform?

 


Comment by: George B. (11/30/2009 5:07:50 PM)

I'm trying to convert a number score into an icon multiple times on the same worksheet...think "Consumer Reports icons". I'm evaluating items in rows based on five criteria listed in columns. Each criteria score ranges from 0-4. The data I have for each item is numeric, but I want to display the icons. I attempted the above, but as you mentioned to James Jones on 4/6/09, you'd need to create a separate range name for each...50 items by 5 criteria, 250 in my case. Any suggestions for a better way?

 


Comment by: Jan Karel Pieterse (12/1/2009 8:29:58 AM)

Hi George,

If it is just icons you want to show, consider using the WINGDINGS font or one of the other symbolic ones (but all symbols need to be in the same font set).
You can use Insert, Symbol to view the available fonts and symbols.

Then use a nested set of IF functions to show the proper symbol. Format the cell with the formula wioth the proper WingDing font and you're done.

 


Comment by: Matt (1/3/2010 1:50:37 AM)

Having some trouble. I'm somewhat new to Excel but I'm currently creating a gear spreadsheet for a game I play. I have a sheet that is pretty much a huge database table with specific items and all the corresponding information for each item. I included a picture in the database for each item. I copy/pasted the picture into excel from a website.

Now on another sheet I have drop down boxes for each particular gear slot that fills in the corresponding information when I make a selection. Example: Drop down box for Helmets contains a list of all the available helmets from my database on the previous sheet and fills in the stats for the helmet I select.

Everything works okay except for the picture. I've tried your method and a few other methods and no matter what I do I can't get the pictures to come up with the rest of the information.

Thank you in advance for your help.

 


Comment by: Jan Karel Pieterse (1/3/2010 11:42:00 AM)

Hi Matt,

Do you need to show just one (changing) picture, or multiple pictures of multiple parts?

 


Comment by: Matt (1/3/2010 8:12:25 PM)

One changing picture per drop down box. There are 15 different gear slots, all having their own row with separate drop down boxes. Exactly like your example only instead 1 there would be 15 unique boxes to choose from.

Example: Drop down box 1 is for the helmets
         Drop down box 2 is for the chest.
         Drop down box 3 is for the gloves.

 


Comment by: Jan Karel Pieterse (1/3/2010 11:24:11 PM)

Hi Matt,

I expect it will prove easier to use the technique shown here:

http://www.mcgimpsey.com/excel/lookuppics.html

 


Comment by: Matt (1/4/2010 3:29:14 PM)

Tried that one already... do you think it has anything to do with the fact that the pictures are copied and pasted onto the worksheet rather than "inserted" from a file?

 


Comment by: Matt (1/4/2010 5:25:30 PM)

Also I was wondering if its possible to keep the same font color from your data table when you select things from a drop down box... say my data table has a mix of items with blue font and purple font... when i pull the info up from the drop down box they default to black. I know you can edit the cell with the drop down box to show a certain color... but there would be different colored font for specific items in the drop down box.

 


Comment by: Jan Karel Pieterse (1/5/2010 2:23:05 AM)

Hi Matt,

McGimpsey's file assumes the pictures are within the file, on the sheet where you want them to show up. You do need to create a list of the picture object names (you can see them in the name box when you select a picture) next to their associated lookup codes. But the example is directed at a single picture, that is correct.

I could help you set things up for multiple pictures if you email me a copy of your file.

On the color of the dropdown: I do not think that can be done. It is either all black or all blue or all red, different colors for different entries are not possible as far as I know.

 


Comment by: Matt (1/7/2010 10:35:53 AM)

Thanks a ton for your help, works perfectly!!

 


Comment by: Morgan (2/17/2010 9:47:40 PM)

Hi! I have tried the solution at
http://www.mcgimpsey.com/excel/lookuppics.html
but in my case, I need to put a different pic in eacch of many different cells on the sheet (as many as 500 or so.)
THe List of pics is the same for each case, I just need a way to have a select box for each of these 500 or so cells.
Can you help me with this?
Best,
B. Morgan

 


Comment by: Jan Karel Pieterse (2/22/2010 7:31:14 AM)

Hi Morgan,

I'm afraid my solution is only useful if you have only a cuople of dynamic pictures you want to show from a list of pictures. Is that what you have?

 


Comment by: B. Morgan (2/23/2010 5:19:17 PM)

There are about 50 pictures.
There may be as many 500 cells in each of which I want to select one of the 50 or so pictures.
Each picture may appear in more than one of the 500 cells.
Imagine that these are slots in a motherboard of a computer.
In some slots I want one type of memory board, in others another memory type, in one a cpu type, in another a MADI IO board, etc.

 


Comment by: Jan Karel Pieterse (2/23/2010 11:05:36 PM)

Hi Morgan,

Suppose your pictures are on sheet "Pictures" (Unique names in column A, pictures in cells in column B). Make sure each picture is completely contained within a cell in column B.
Put this code behind the sheet in which you want to show them:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oPic As Picture
    Dim oCell As Range
     With Target
        On Error Resume Next
        Set oCell = Sheets("Pictures").UsedRange.Find(What:=.Value, After:=Sheets("Pictures").Range("A1"), LookIn:=xlFormulas, _
                                                                 LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                                                 MatchCase:=False, SearchFormat:=False)
        If Not oCell Is Nothing Then
            For Each oPic In Me.Pictures
                If oPic.TopLeftCell.Address = .Offset(, 1).Address Then
                    oPic.Formula = "=" & oCell.Offset(, 1).Address(external:=True)
                    Exit For
                End If
            Next oPic
        End If
    End With
End Sub


On the sheet where you want to show them, you need two columns. The first column holds the name of the item to show, the second column holds a picture object from the drawing toolbar, with it's top-left corner INSIDE that cell. Set up those picture objects as described in this article and paste the code shown above in the VBA code module belonging to the worksheet in question.

 


Comment by: Gert Fourie (3/11/2010 11:55:03 PM)

Hi -

I am using excel 2007. I have tried your example but the pictures do not display. Any advice? I can send you my file.

Thanx

 


Comment by: Jan Karel Pieterse (3/13/2010 10:50:34 AM)

Hi Gert,

You can send me the file, but it may take quite some time before I respond because I'm a bit tied up doing paid work :-)

 


Comment by: Johnny (3/15/2010 12:48:55 PM)

I'm looking to load PNG images embedded in a worksheet onto a userform image control. I'm using loadpictureGDI function from a MS MVP and have no problem loading a file from disk.

I am having trouble with the syntax to set me.image_1.picture to an an image embedded in a worksheet.

Tried me.image_1.picture = worksheet(1).shapes(2).picture

Any ideas?

 


Comment by: Jan Karel Pieterse (3/16/2010 11:12:46 AM)

HI Johnny,

The only way I know is by placing an image on top of a chart, then using the Export method of the chart object to save the chart as an image and finally, using your loadpicturegdi method.

 


Comment by: Muhammad Sami (4/18/2010 3:16:57 AM)

I want to use formula vlookup to add picture in excel but in one cell one picture also please tell me how to convert somany jpg picture into text file by using ms dos

 


Comment by: Jan Karel Pieterse (4/18/2010 3:17:52 AM)

Hi Muhammad Sami,

WHat exactly do you mean by converting jpg pictures to text file?

 


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!

 


Comment by: Scott (9/27/2010 5:41:38 PM)

This is great...thanks. The only issue that I have run into is that the image is not printing. If I use a border around the image, I can print the line but not the picture.

 


Comment by: Jan Karel Pieterse (9/28/2010 1:01:58 AM)

Hi Scott,

Have you set the picture to print? (right-click, Format picture, propeties tab; Both the original pictures and the dynamic picture should be set to print). If so, is the picture completely within the print area?

 


Comment by: Scott (9/28/2010 6:13:55 AM)

Hi Jan,

It appears that all pictures are set to print. When I ungroup the pictures to reveal the dynamic picture, it is set to print as well, but the size changes. It is within the print area though.

 


Comment by: Jan Karel Pieterse (9/28/2010 6:57:01 AM)

Hi Scott,

No idea what's wrong. Can you send me your file (see address below)?

 


Comment by: dan.c.dugan@gmail.com (10/1/2010 4:19:11 PM)

I followed the instructions from above, however, I must have to change some setting in excel. When I select my picture from the drop-menu, the dynamic picture does not change. If I then click on the dynamic picture and then click on the command line (which displays ' =Picture ') then hit enter, the picture will update itself to the selection I made in the drop-down.

I have verified that I have Auto Calculation on in the Tools, Options, Calculations tab. Any thoughts?

 


Comment by: Jan Karel Pieterse (10/2/2010 5:24:06 AM)

Maybe adjusting the Picture name helps:

=OFFSET(Sheet1!$B$2,MATCH(Sheet2!$A$2,PictureList,0)-1,0*NOW(),1,1)
(it will make the function volatile)

 


Comment by: gsm (11/11/2010 3:32:00 PM)

Wonderful, i used your method to run something simple; need help with next step;

lets assume three groups one, two, three as list names;

List one has a,b,c; List two has c,d,e; list three has f,g,h all in sheet one and named;

Sheet 2 has a-h in col 1 with its picture aside- copy pic of same alphabet;

sheet 3- would like to data validate -list one,two, three; choose sublist of apprp alphabets and i want picture chosen by the sub list...

need help pl pl

 


Comment by: Jan Karel Pieterse (11/11/2010 10:52:31 PM)

I think the first thing to do is to create the dependent data validation dropdowns, see:
http://www.contextures.com/xlDataVal02.html

 


Comment by: Justin (12/10/2010 12:39:04 PM)

I have been testing this method in order for my employees to select their names from a dropdown and it will populate an image of their electronic signature in the field above it.
In my instance, the names are in Source!A2:A7 and the corresponding image is in Source!B2:B7. The dropdown is in ApprReviewForm!C64 and the image should display in ApprReviewForm!C63.
In testing, when I select the first name in the dropdown their signature image populates as it should. When I select any other name in the dropdown, the image in C63 is blank.

Below is what I used to define the two names:

Picture
=OFFSET(Source!$B$2,MATCH(ApprReviewForm!$C$64,PictureList,0)-1,0,1,1)
PictureList
=OFFSET(Source!$A$1,1,0,COUNTA(Source!$A:$A)-1,1)

Do you have any advice on what may be causing the issue? Thank you for your time.

For reference, I am using Excel 2003.

 


Comment by: Jan Karel Pieterse (12/10/2010 2:32:26 PM)

Hi Justin,

The fact that the first one works seems to indicate that you've set up correctly.

Are you sure the names in the dropdown actually match the names on the list? To easily see what is going on, use an empty cell and enter the match function in it:

=MATCH(ApprReviewForm!$C$64,PictureList,0)

 


Comment by: Justin (12/14/2010 7:02:07 PM)

Jan,
Thank you for the prompt reply. When I use the match function, there appears to be an issue with the last name on the list. Using the name in Source!A7 in C64 does not display the image in Source!B7, instead it displays #N/A. Any ideas?
Also unfortunately the images for B4:B6 are currently blank because the employees still haven't supplied me with their electronic signatures. Would the fact that those fields are blank be causing the issue? Please let me know. Thank you for all your help.

 


Comment by: Jan Karel Pieterse (12/15/2010 12:03:55 AM)

Hi Justin,

The NA means you have misspelled the name in either place (check if there are no leading or trailing spaces, or maybe multiple spaces in-between).

The absence of a picture makes no difference. To be able to check, just write the person's name in those empty cells.

 


Comment by: Justin (12/20/2010 12:17:27 PM)

Jan,
I found the issue. The names are from a drop-down box and the first field was blank (to be the default when the excel file opens). For some reason just having an empty cell was causing issues. Once I placed " " in the cell, the formula works perfectly. Thank you for all your help. This was the most informative site by far. Again, thank you.

 


Comment by: Jan Karel Pieterse (12/20/2010 10:21:48 PM)

Hi Justin,

Great, thanks for letting us know!

 


Comment by: Fabrizio (1/22/2011 3:39:09 AM)

How can I simply show a picture in a cell by referring and having it in another worksheet of the same workbook?

 


Comment by: Jan Karel Pieterse (1/22/2011 8:39:12 AM)

Hi Fabrizio,

Just follow the steps that explain how to add the dynamic picture, but do not do the last step in the description which says to enter the =picture formula. Instead, point that formula directly to the cells that span the picture.

 


Comment by: Mark D (1/28/2011 9:51:46 AM)

Hi Jan,

Thanks for this guide. It works great for me.

I have one question for you. I would like to output different images in multiple cells. Is there an easy way to open the range of cells included in the Picture formula?

I have indicated what I'm trying to do below:

=OFFSET(Sheet1!$B$2,MATCH(Sheet2!$C$5:$C$20,PictureList,0)-1,0,1,1)

I have managed to acheive this by creating =Picture2, and changing the formula to:

=OFFSET(Sheet1!$B$2,MATCH(Sheet2!$C$6,PictureList,0)-1,0,1,1)

 


Comment by: Jan Karel Pieterse (1/28/2011 12:36:32 PM)

Hi Mark,

I'm afraid you do need a separate Picture1, Picture2, .. name for each picture.

 


Comment by: Rich (2/17/2011 12:30:46 PM)

Awesome! This method of showing an image is really great! I needed this for a from/worksheet which shouldn't use VBA (macro security is required).

 


Comment by: Abhay Sasmal (2/18/2011 1:53:46 AM)

Hi
I am trying to insert picture as dynamical in excel by using
this formula, picture is not shown on proper cell after select option.pictureList=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1)
picture =OFFSET(Sheet1!$B$2,MATCH(Sheet2!$A$2,PictureList,0)-1,0,1,1)
Pls provide solution.

 


Comment by: Jan Karel Pieterse (2/20/2011 11:06:23 AM)

Hi Abhay,

I'm not sure I understand your question, but if the picture does not show the proper cell, maybe you got the OFFSET formula wrong in the Picture range name?

 


Comment by: Krzysztof (2/24/2011 6:54:34 AM)

This solution is awsome. I manage to create a nice spread sheet where you can chooce a cheese and different picture of that cheese will appear. However for some reason I cant print those pictures. If I go to print preview I can only see empty square without picture in it. Any idea?

 


Comment by: Jan Karel Pieterse (2/24/2011 10:20:13 AM)

Hi Krzysztof,

I have no idea! If you right-click the picture and look at it's properties, does it have the print box checked?

 


Comment by: Carlos (2/28/2011 10:27:09 PM)

Hi Jan,
I can't copy and paste the same formula in other parts of the book, cause everything is going ok with the picture list, but the picture doesn't change according to the picture list. It only changes when I change the original. Any suggestion
Thanks very much

 


Comment by: Jan Karel Pieterse (3/1/2011 1:58:10 AM)

Hi Carlos,

Are you trying to create a second dynamic picture by any chance?
If so, you'll need a second copy of the range name called 'Picture', which needs to point to the proper cell for its lookup value.

 


Comment by: YuB (3/10/2011 5:04:54 PM)

Will this method work if the cells are merged?

 


Comment by: Jan Karel Pieterse (3/13/2011 11:43:14 AM)

Hi Yub,

I guess the only way to find out is by trying!

 


Comment by: Kelsey (3/18/2011 11:07:10 AM)

I LOVED this tutorial and can get it to work perfectly with pictures, however, I tried it with dynamic Charts and it worked somewhat but had an unusual problem that I cannot figure out why sometimes the charts would show up perfectly and then sometimes it'd just show that blank bitmap object??? but it ALWAYS shows up on the print preview?! Is getting a dynamic chart to show up flawlessly this way a lost cause or is it something simple I'm missing? again it always shows up perfectly in the print preview...

 


Comment by: Jan Karel Pieterse (3/18/2011 11:57:10 AM)

Hi Kelsey,

I'm afraid I don't know. Maybe it has something to do with your printer driver (yes, that sometimes happens), or maybe it is just Excel. Sometimes hitting page-down/page-up convinces Excel to refresh the display.

 


Comment by: Shea (4/1/2011 11:48:53 AM)

I am using Excel 2010 and get the warning

"Reference is not valid" when I attempt to change

=EMBED("Paint.Picture","") to

=Picture.

Any ideas?

Thanks for the forum and the help!

 


Comment by: Jan Karel Pieterse (4/3/2011 5:10:44 AM)

Hi Shea,

There must be an error in the Picture name. Hit Escape to go back to Excel, then hit F5 and type Picture and click OK, do you get the same error?

 


Comment by: vivi (5/18/2011 1:47:13 AM)

Hi,

Thank you for the tutorial.
I can show the picture when I input the value. But on some pictures, they don't show fully, only a little part.
Do you have an idea about that?

Thank you

 


Comment by: Jan Karel Pieterse (5/18/2011 2:01:25 AM)

Hi Vivi,

Check that particular picture in the list of pictures; probably the picture is larger than it's underlying cell.

 


Comment by: Vivi (5/26/2011 5:57:15 PM)

Hi Jan,

Thanks for previous help, I changed the size of pictures and it works..

Now I have another problem..
I make a picture using arrows and lines in excel and group them together. How do I show it when I use those formulas above because it is not a bitmap image?

Thank you

 


Comment by: Jan Karel Pieterse (5/27/2011 2:40:30 AM)

Hi Vivi,

Anything in the cell can serve as a "picture", regardless of its format. As long as whatever it is you want to show is completely contained in the cell(s) the Dynamic range name called "Picture" points to.
So even a chart or just the cell contents itself will appear as the dynamic picture.

 


Comment by: Ben (6/10/2011 4:33:51 AM)

Previously, people were having issues when using Excel 2010

Reference is not valid error is solved by inserting a 'picture', not an 'object', in the Creating the picture step.

 


Comment by: Vimal (6/13/2011 5:33:41 AM)

Dear Jan,

Thanks for suggesting this kind of suggestion,
But when i am trying to use the same formula with MS-2007,

| am getting just a sigle picture in Cell B2 on Sheet 2, May be i am doing something wrong,

could you pls more more clarify on following points.
1. Excel 97 to 2003
2. Making the picture dynamic

Thanks in Advance

 


Comment by: Vimal Kumar (6/17/2011 2:55:43 AM)

Hi Jan,

I loved your given formula,
I have a question for you at presently I am using just single criteria it means if on Sheet 2, if Cell A2= maximize, One picture will appear with reference to Sheet A1,

In my xls sheet there are two selection criteria i.e. In two cells A2, B2 data validation command is used, then the picture should appear in Cell C2,

I want to use multiple selection criteria for a single picture,
pls suggest,
Thanks in advance
Regards
Vimal

 


Comment by: Jan Karel Pieterse (6/17/2011 3:53:02 AM)

Hi Vimal,

Suppose you would have to look up just a value, not a picture; what formula would you use to get that value from the list?

 


Comment by: Vimal Kumar (6/17/2011 4:50:15 AM)

Dear Jan,

I generally use the logic function for this purpose,
i.e.= IF(AND($C$2=$W$3),R2,IF(AND($C$2=$W$4),S2,0))
But here i want to get the picture not value

Pls suggest
your early reply will be more appreciable.

 


Comment by: Jan Karel Pieterse (6/18/2011 11:17:08 AM)

Hi Vimal,

I'm very sorry, but I still am not sure what you need.
Please email me your workbook (see address near bottom of page) and try to explain what you need in your file.

 


Comment by: Tony (6/20/2011 8:12:05 AM)

Jan,

Thanks for formula. I am getting error:

"Reference is not valid" when I change =EMBED("Paint.Picture","") to =Picture

Ben commented(6/10/2011 4:33:51 AM)

"Previously, people were having issues when using Excel 2010

Reference is not valid error is solved by inserting a 'picture', not an 'object', in the Creating the picture step."

Do you just insert any picture? When i do this and type =picture in the formula bar i still receive the same error listed above.

Thank you in advance - Tony

 


Comment by: Jan Karel Pieterse (6/21/2011 1:19:42 AM)

The easiest way is to add the camera tool to the QAT and use that. rightclick your QAT, select customize and look in the commands not in the ribbon category.

 


Comment by: me (6/26/2011 5:08:54 PM)

Thank you very much, this is exactly what i was looking for !

 


Comment by: Dianne (7/27/2011 11:55:24 AM)

Hello,
In the same workbook: I made a Master spreadsheet with all my project data that is pulled from other spreadsheets (using paste link). Then I made another spreadsheet with a dropdown list of all my projects & when one is selected, all the required information is populated into the spreadsheet(I used the index function for this). However, I want to add camera objects that will populate when a cetain project is selected as well. How do I do this? I have been working on this for over a month now, researching & testing but I can't seem to get it to work. I am not familiar with code so if you could recommend a way using formulas or functions that would be of great help. Thank you.

 


Comment by: Jan Karel Pieterse (8/22/2011 3:02:56 AM)

Hi Dianne,

Can't you get it to work using the explanation on this page?

 


Comment by: Chandrashekhar (10/21/2011 11:42:42 PM)

Hi, Thanks for the Topic "Showing a picture from a list of pictures". I downloaded the sample excel sheet also. It was very useful for me.If I need various picture to be shown in the column B (B3, B4, B4,.....) of Sheet2 according to description of column A( A2,A3,A4...). Eg. B2 of Sheet2 indicates "Maximise" Symbol(A2), If I need A3 to indicate "Minimise", what i should do?. I tried a lot with copy paste, defining name, Ref etc. Kindly update in your sample sheet and send to me.
I really appreciate your efforts in making this useful informs.
Thanks and regards.
Chandrashekhar

 


Comment by: Jan Karel Pieterse (10/24/2011 1:51:41 AM)

Hi Chandrashekhar,

FOr each picture you want to show, a new copy of the defined name "Picture" is needed. You can simply number them, like this:

Name1: Picture1
Refersto: =OFFSET(Sheet1!$B$2,MATCH(Sheet2!$A$2,PictureList,0)-1,0,1,1)

Name2: Picture2
Refers to: =OFFSET(Sheet1!$B$3,MATCH(Sheet2!$A$3,PictureList,0)-1,0,1,1)

Name3: Picture3
Refers to: =OFFSET(Sheet1!$B$4,MATCH(Sheet2!$A$4,PictureList,0)-1,0,1,1)

For each picture, you use the corresponding Picture1,Picture2,... name.

 


Comment by: Milind Kale (11/22/2011 4:15:13 AM)

Dear Sir,
    How to insert icon file in excel cell through VB6 because I made all reports in excel.I want to use checkmrk.ico but this icon should be excel cell size.
    If you have code just sen me.I will learn from u and use this code in my application.
    Thank you very much.

 


Comment by: Jan Karel Pieterse (11/22/2011 7:51:42 AM)

Hi Milind,

I am not sure what you are trying to do, can you please try to explain?

 


Comment by: Andy (12/10/2011 9:59:53 AM)

I admit I'm struggling to understand this example but am starting to get it. My project involves having someone's picture appear in a dashboard-type sheet, depending upon whose name is selected from a drop-down list. The data is kept in a second sheet, including the pictures. However, since the list could change over time, I would like to be able to use a lookup function to search for the appropriate picture to show based on the name selected. I can't use VBA so am trying to utilize formulae. Any help you can offer would be greatly appreciated!! I'm using MS Excel 2007.

 


Comment by: Jan Karel Pieterse (12/11/2011 11:11:20 PM)

Hi Andy,

How can I be of help? In principle, this page describes all the steps needed to get exactly what you ask for...

 


Comment by: Andy (12/12/2011 6:20:23 AM)

Thanks much, Jan - I may be lacking some fundamental knowledge needed to make the small leap from this page's instructions to what I'm trying to do. I just can't figure out how to get offset, match, index and vlookup to work together in the right way to accomplish an image changing based on the selection from the drop-down list (the drop-down list is populated from a changing list on a second sheet; essentially, I have it looking at several hundred rows even though there are only a few dozen people at this time).

 


Comment by: Jan Karel Pieterse (12/12/2011 6:33:56 AM)

Hi Andy,

If you like you can send me the file and I'll have a look.

Just make sure you tell me where to look for the pictures and where the dropdown is that selects a name of which the pic is to be shown, so I don't have to work my way through your file and try to figure that out.

 


Comment by: Andy (12/12/2011 1:16:00 PM)

Thanks again - what's the best way to get it to you?

 


Comment by: Jan Karel Pieterse (12/13/2011 2:02:33 AM)

Hi Andy,

You can send it by email, address at the bottom of this page.

 


Comment by: lim ck (1/2/2012 10:02:43 AM)

Can the be use on live picture instead? meaning, the worksheet camera tool able to give command to download the photo i took directly?

Many thanks
lim

 


Comment by: Jan Karel Pieterse (1/2/2012 10:15:38 PM)

Hi Lim,

No, this example requires the picture to be placed on the worksheet.

 


Comment by: Yuda (1/15/2012 1:43:04 AM)

Thanks very much... (^_^)

 


Comment by: Kylin (2/15/2012 1:17:35 AM)

Hi Jan,

Thanks for sharing.
I used the techinique and find it very useful. But sometimes the picture is fully-shown on one PC while half-shown on another; or half-shown on one PC while prefectly well on another.

Do you have any clue what is causing the problem?

Thanks,
Kylin

 


Comment by: Jan Karel Pieterse (2/15/2012 2:37:37 AM)

Hi Kylin,

One thing to look out for is freeze panes, it sometimes causes trouble. Turn off WIndow, Freeze panes for the sheet with the source list of pictures, perhaps that helps?

 


Comment by: Kylin (2/15/2012 4:14:31 AM)

Thanks Jan. I will check with my workmate.

 


Comment by: Rob M (3/12/2012 3:30:01 PM)

I am OK until I get to Step 6. I cannot click on the image, and then access the Formula Bar, it stays in Picture Tools until I click off the image. (I am using other images than those used in the example, I find Clip Art and shapes work, gif and jpg images don't. Any ideas?

 


Comment by: Jan Karel Pieterse (3/12/2012 10:50:29 PM)

Hi Rob,

Did you select the type "Bitmap image"?

 


Comment by: Andrew B (3/15/2012 3:24:48 PM)

Hi All,

Has anyone figured out why the image is not printing properly all my cells are set to print but in preview and the print out the images are only half there.

 


Comment by: Ted Hartnell (3/23/2012 12:50:46 AM)

Hi Jan:

I liked this approach, but I needed a solution that allowed me to insert many pictures into the final lookup tab. So I came up with a VBA solution. The code is more than 2000 characters so I can't paste it here. But I'd also be happy to send you a sample spreadsheet if you want to link to this thread.

Let me know if you want the sample spreadsheet.

Ted.

 


Comment by: Jan Karel Pieterse (3/23/2012 3:56:35 AM)

Hi Ted,

Thanks for letting us know.

I expect your solution is similar to the one by Bernie Deitrick (as mentioned above the comments):

http://www.contextures.com/excelfiles.html#DV0049

 


Comment by: Nico Moreno Vicari (4/5/2012 5:23:24 PM)

Hi Jan,
After many attempts, I was able to get pictures of flags to show up after selecting a given country from a drop-down menu with country names. So, this works great! (I used the extended VBA code you suggested to someone before).
However, I am running into the folling problem. Even though the object box displays the correct flag beautifully, when I go to print preview I see part of a picture and about half of another picture - both unrelated to the one I need. Do you know what might be causing this? All pictures are set to print.
Thanks for your feedback!
Nico

 


Comment by: Jan Karel Pieterse (4/9/2012 10:09:14 AM)

Hi Nico,

Are there multiple pictures on top of each other by any chance?

 


Comment by: b.ramakrishnan (6/12/2012 11:31:47 AM)

HI! Thank you for this wonderful post! I got it to work the way I wanted. But I now face a peculiar problem. When I try to save this sheet containing this dynamic object as aPDF using the Save As "PDF" option in Excel 2007, I find that the page gets saved, but the in the place of the embedded picture I just see a blank box and also the PDF file gives an error when I try to open it with Acrobat reader. I do not have this problem if there is no embedded object. Also another input- if I use a third party software like PDF995 to save this as a PDF, I have no problem. Is it an Excel 2007 Bug? Can you help? I am not sure this is 100% relevant to your post

 


Comment by: Jan Karel Pieterse (6/12/2012 2:04:35 PM)

Hi,

Sounds like a problem with Excel 2007 indeed.
Does it work if you select a different printer before doing the save-as to pdf?

 


Comment by: B.ramakrishnan (6/12/2012 4:36:05 PM)

Yes you are right! It is an Excel bug. I got it to work with PDFcreator ! I have stopped using the built-in PDF Add-in

 


Comment by: Jan Karel Pieterse (6/12/2012 4:38:11 PM)

Hi,

Thanks for lettting me know.

 


Comment by: sandesh nilvarna (6/20/2012 8:40:33 AM)

I would like to know that what formula can we use to copy image from one cell to other cell (other than copy paste)?

 


Comment by: Jan Karel Pieterse (6/20/2012 9:54:38 AM)

Hi sandesh,

I am not sure what you mean?

 


Comment by: Jo Miller (7/19/2012 3:20:51 PM)

Thanks for your help - I've been struggling with adding pictures for a little while now. Your explanation and sample file helped me out no end!

 


Comment by: Neena (8/1/2012 6:46:25 AM)

Like DAta validation,is there any way to select the logo from a drp down list.

 


Comment by: sandesh Nilvarna (8/3/2012 1:34:15 PM)

Like how we can fetch data in excel using VLOOKUP. But can we use any function to import image in excel from my picture folder?

 


Comment by: Jan Karel Pieterse (8/7/2012 11:01:25 AM)

Hi,

@Neena: One way to do this would be by customising the Excel ribbon by adding a gallery control to it.

@Sandesh: Not by using built-in Excel functions I'm afraid.

 


Comment by: Lynn C. (8/26/2012 8:07:16 PM)

I followed your step to the dot (at least I thought so!) but I got "reference not valid" error message. I even used your sample file and try to redo the bitmap step and got the same error. Is it because I am using Excel 2010? :(

 


Comment by: Jan Karel Pieterse (8/27/2012 2:40:41 PM)

Hi Lynn,

If you like you can send me your current file (see address near bottom of this page).

 


Comment by: Jonathan R (8/30/2012 4:56:58 PM)

Hi,

It worked but How can I do the same on the second line, it always shows the first picture I selected?

Also if I want to repeat this for multiple rows, what are the fastest ways to copy it?

thanks!

 


Comment by: Jan Karel Pieterse (8/31/2012 11:33:14 AM)

Hi Jonathan,

Unfortunately, this technique requires you to set up a separate "Picture" range name for each picture you want to show.

So you have to define Picture01 for the first dynamic picture (pointing to its own cell), Picture02 for the second picture (pointing to the second cell) and so on.

Then for each picture, use the appropriate PictureXX name as its formula.

 


Comment by: Azeem (9/29/2012 9:19:24 AM)

Please suggeest: I am geeting picture in my cell but wehn I go to print 2-3 pictures are showing in the object (Pict Cell) What to do? please hel

 


Comment by: Jan Karel Pieterse (10/1/2012 10:24:37 AM)

Hi Azeem,

Perhaps the picture is referring to too many cells?

 


Comment by: Azeem (10/1/2012 10:43:06 AM)

The picture is not referring to too many cells. The pictures are in different rows next to each person name in Picture column and name in Name column. And i am working in Excel 2007.

 


Comment by: Jan Karel Pieterse (10/2/2012 9:23:55 AM)

Hi Azeem,

Please email your workbook to me, address below...

 


Comment by: Sabzero786 (11/13/2012 9:44:35 AM)

Hi Guys,

i am getting the same error as Lynn above... "Reference not valid", how do i overcome this?

I followed your step to the dot (at least I thought so!) but I got "reference not valid" error message. I even used your sample file and try to redo the bitmap step and got the same error. Is it because I am using Excel 2010? :(

 


Comment by: Jan Karel Pieterse (11/13/2012 11:34:54 AM)

Hi SabZero,

Can you perhaps email the workbook, so I can have a look?
Please refer to this page in your email, otherwise I might ignore your message!
Email address on bottom of this page...

 


Comment by: DaveB (11/21/2012 3:30:54 PM)

Hi Jan,
I'm having a problem using this in Excel 2010. I've copied every instruction to the letter into a new Workbook. Everything goes fine until I Insert Object. Here's what happens:
1. Insert Object.
2. Choose Bitmap Image
3. Click OK.
4. Paint opens.
5. I close OR minimise Paint (doesn't matter which I do, the result is the same)
6. Modify Formula Bar to read =Picture
7. Error: Microsoft Excel - Reference is not valid.

At this point it won't allow me to close Excel. I have to use CTRL-ALT-DEL.
Is your work above incompatible with Excel 2010?

 


Comment by: Jan Karel Pieterse (11/22/2012 11:29:45 AM)

Hi Dave,

Perhaps the range name is causing the error. What happens if you hit F5 and then enter the range name "Picture" (without the quotes) into the box and click OK?

 


Comment by: DaveB (11/23/2012 1:31:01 PM)

Hi Jan,
At what point do you want me to press F5? If I do it before I Insert Object it says "The text you entered is not a valid reference or defined name". If I try to press F5 after I Insert Object, it won't allow me, it just repeatedly says "Reference is not valid" and I have to CTRL-ALT-DEL out of Excel.

 


Comment by: Jan Karel Pieterse (11/23/2012 2:48:16 PM)

Hi Dave,

Prior to inserting the picture. The fact that you get an error indicates the range name is incorrect. Perhaps you need to enter a valid entry in the cell that is supposed to trigger which picture to display.

 


Comment by: DK Dhir (2/6/2013 8:35:28 AM)

Sir I have Excel 2007 file named "DTPH.xlsx" with data having person names, details in column "A to K". I have to insert the person pictutre in column "M" with the data. In the datasheet column "L" I have entered the picture file names also. I am inserting the pictures manually from a particular folder "D:\pictures". Do you have any macro program for inserting pictures from that particular folder to the data sheet.

 


Comment by: Jan Karel Pieterse (2/7/2013 8:35:07 PM)

Hi DK,

Please ask this question at http://www.eileenslounge.com

 


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: Chandra (7/16/2014 9:02:57 PM)

I have excel file with 2500 customers in column "A". I want to use vlookup or any VBA Macro to get the images from the folder which had images (logos) and want to insert in column "B" with corresponding customer name in column "A".

if no images find for the customers, then I need "N/A" in column "B" for that customers.

Any help and guidance is appreciated.

Chandra

 


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.

 


Comment by: Phil (7/23/2014 4:28:29 PM)

This works great for the worksheet I'm using except for the limit it has. It seems to only be active for around 2727 rows and then it refuses to display any pictures on rows after that. For example, if I put a picture in row 2727, the picture is displayed. But if I put a picture in row 2728, the picture will not display when I type in the corresponding number.

Do you know how I can expand the range of the dynamic formula?

 


Comment by: Jan Karel Pieterse (8/12/2014 11:08:05 AM)

Hi Phil,

Perhaps there is a problem with your range names?

 


Comment by: Chuck (10/14/2014 3:59:36 PM)

I have attached images as comments (Excel 2007)...but cannot get them to print even when I have set to "print comments at end of sheet". The image itself will not print. I am using this as a "cost sheet" and the comment is an image of the invoice. I want to be able to print the cost sheet with the invoices printing out as well. Any suggestions?

 


Comment by: Jan Karel Pieterse (10/14/2014 5:14:09 PM)

Hi Chuck,

If you go into File, Options, Advanced, you can set comments to always show. Then if you select Page Layout and click the tiny arrow bottom-right on the Page Setup group and subsequently click the "Sheet" Tab, you can set comments to print "As displayed on sheet". That will print.

 


Comment by: Chuck (10/14/2014 6:31:33 PM)

Thanks so much for you quick response...but I am not seeing the "File, Options, Advanced" part anywhere.
But I did try setting it to "as displayed on sheet" and the print preview does show that it will print them like that..that's way better than I was getting! I was really hoping to be able to just print them at the end.
I appreciate your help!!

 


Comment by: Jan Karel Pieterse (10/15/2014 7:51:15 AM)

Hi Chuck,

Which Excel version are you using?

 


Comment by: Chuck (10/15/2014 2:19:09 PM)

I have Excel 2007.When I checked for the comments to be "as displayed on sheet", it did show the images in the print preview, it did not, however, actually print them. I found that I can put them in another cell by clicking "insert" picture, but that shows them HUGE! My goal is to get the images of invoices to print at the end like regular comments do.

 


Comment by: Jan Karel Pieterse (10/15/2014 4:36:32 PM)

Hi Chuck,

I don't think you can do that "out of the box", but perhaps you can using a macro.

 


Comment by: Hunain H Bilgrami (11/5/2014 8:28:33 AM)

Hey,

This is very nice sheet but i have some problem. The problem is this if i want to insert picture in over all B coloum not only Sheet2 Cell B2. What is the formula? can you create this sheet

 


Comment by: Jan Karel Pieterse (11/10/2014 11:36:17 AM)

Hi Hunain,

The method I describe here is not the right solution if you have many dynamic pictures to show. Check out the link I placed under "Other solutions" for a VBA driven approach.

 


Comment by: mark (3/17/2015 2:22:25 PM)

This worked really well for the first instance, but I have a worksheet that has 4,200 rows that I would like to repeat this on. The problem I'm having is that when I copy down the data validation list all the way down the column, and copy/paste the image box, whenever I change one of the drop-down values in the 4,200 rows all of the icons change to that selection.
How can I make it so that each person can select an option from the drop-down and have the corresponding image appear in just that row?

 


Comment by: Jan Karel Pieterse (3/17/2015 4:32:19 PM)

Hi Mark,

I advise you to look at the "Other solutions" section of this page, my solution isn't very useful with that many pictures.

 


Comment by: tbennett (3/7/2017 10:25:16 PM)

How do you get the boxes around the images NOT to show up? On your Sheet 1 they aren't there but on Sheet 2 there is a box around the image. It did the same thing with my flags. Is there anyway to get rid of those? Thanks!

 


Comment by: Jan Karel Pieterse (3/8/2017 9:49:50 AM)

Hi tbennet,

That is a matter of selecting the picture opbject and changing its property:

- Right-click the pic, select Format Picture
- Click Line color and select No line

 


Comment by: Maria (7/19/2017 1:52:26 PM)

I downloaded your sample it it works great.
I need to set up 3 drop downs like this on the same sheet, and I can't figure out how to copy the other 2 sets.
Please help : )

 


Comment by: Jan Karel Pieterse (8/13/2017 5:06:02 PM)

Hi Maria,

You will have to create a set of range names for each dropdown, precisely as in the example, but each having different range names of course.

 


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].