Showing a picture from a list of pictures
Content
- Introduction
- Preparing the list
- Preparing the sheet that shows the dynamic picture
- Creating the picture
- Making the picture dynamic
- Download
- Other solutions
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:
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:
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.
- 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.
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 (18-11-2008 05:27:24) deeplink to this comment
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 (18-11-2008 07:46:03) deeplink to this comment
Hi viswanath,
Not sure what you are trying to accomplish, could you elaborate?
Comment by: viswanath (18-11-2008 20:06:28) deeplink to this comment
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 (18-11-2008 22:17:07) deeplink to this comment
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 (17-12-2008 00:14:59) deeplink to this comment
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 (17-12-2008 02:19:02) deeplink to this comment
Hi Vinod,
I am not sure I understand what you are asking, could you please explain a bit more?
Comment by: vinod (17-12-2008 02:52:01) deeplink to this comment
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 (17-12-2008 03:21:31) deeplink to this comment
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 (17-12-2008 13:05:02) deeplink to this comment
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 (18-12-2008 01:10:07) deeplink to this comment
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 (7-2-2009 16:11:51) deeplink to this comment
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 (8-2-2009 22:47:48) deeplink to this comment
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 (21-3-2009 08:47:39) deeplink to this comment
Hello Jan,
Could you explain if this can be translated onto a form?
Thanks very much,
Josh
Comment by: Jan Karel Pieterse (21-3-2009 11:39:27) deeplink to this comment
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 (5-4-2009 05:51:59) deeplink to this comment
is it possible using this method to have multiple changable images on a sheet dependant on list name?
Comment by: Jan Karel Pieterse (6-4-2009 00:46:55) deeplink to this comment
Yes, but you'll have to create range names for each of them, which is a bit cumbersome.
Comment by: Todd G (11-5-2009 18:47:04) deeplink to this comment
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 (12-5-2009 21:57:07) deeplink to this comment
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 (13-5-2009 19:17:01) deeplink to this comment
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 (13-5-2009 23:34:58) deeplink to this comment
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 01:47:27) deeplink to this comment
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 01:49:03) deeplink to this comment
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 (28-8-2009 09:18:10) deeplink to this comment
Brilliant page. Still struggling with having no pictures displayed when customer open my form... but getting there.
Thx a mil!!
Jerome
Comment by: leila (9-10-2009 00:16:44) deeplink to this comment
how can load picture to use in form with vlookup excel vba?
Comment by: Jan Karel Pieterse (9-10-2009 05:16:39) deeplink to this comment
Hi Leila,
Not sure what you are asking. Do you want the picture to appear on a VBA Userform?
Comment by: George B. (30-11-2009 17:07:50) deeplink to this comment
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 (1-12-2009 08:29:58) deeplink to this comment
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 (3-1-2010 01:50:37) deeplink to this comment
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 (3-1-2010 11:42:00) deeplink to this comment
Hi Matt,
Do you need to show just one (changing) picture, or multiple pictures of multiple parts?
Comment by: Matt (3-1-2010 20:12:25) deeplink to this comment
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 (3-1-2010 23:24:11) deeplink to this comment
Hi Matt,
I expect it will prove easier to use the technique shown here:
http://www.mcgimpsey.com/excel/lookuppics.html
Comment by: Matt (4-1-2010 15:29:14) deeplink to this comment
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 (4-1-2010 17:25:30) deeplink to this comment
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 (5-1-2010 02:23:05) deeplink to this comment
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 (7-1-2010 10:35:53) deeplink to this comment
Thanks a ton for your help, works perfectly!!
Comment by: Morgan (17-2-2010 21:47:40) deeplink to this comment
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 (22-2-2010 07:31:14) deeplink to this comment
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 (23-2-2010 17:19:17) deeplink to this comment
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 (23-2-2010 23:05:36) deeplink to this comment
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:
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 (11-3-2010 23:55:03) deeplink to this comment
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 (13-3-2010 10:50:34) deeplink to this comment
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 (15-3-2010 12:48:55) deeplink to this comment
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 (16-3-2010 11:12:46) deeplink to this comment
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 (18-4-2010 03:16:57) deeplink to this comment
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 (18-4-2010 03:17:52) deeplink to this comment
Hi Muhammad Sami,
WHat exactly do you mean by converting jpg pictures to text file?
Comment by: Salahuddin (20-4-2010 10:37:04) deeplink to this comment
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 (20-4-2010 22:11:19) deeplink to this comment
Hi Salahuddin,
The article comes with an example file to download. See the section called "Download".
Comment by: Sam (15-6-2010 10:21:46) deeplink to this comment
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 (16-6-2010 00:31:11) deeplink to this comment
Hi Sam,
You can send me the file, I'll have a quick look free of charge.
Comment by: Allison (16-6-2010 07:50:04) deeplink to this comment
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 (25-6-2010 13:48:44) deeplink to this comment
brilliant!!! I like the way you solved the problem, simple yet effective.
Comment by: Louise (8-7-2010 11:18:25) deeplink to this comment
Many thanks - just what I needed and an easy solution(but only easy because of your very clear instructions).
Comment by: Jan Karel Pieterse (8-7-2010 11:18:50) deeplink to this comment
Hi Louise,
You're welcome!
Comment by: Scott (27-9-2010 17:41:38) deeplink to this comment
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 (28-9-2010 01:01:58) deeplink to this comment
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 (28-9-2010 06:13:55) deeplink to this comment
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 (28-9-2010 06:57:01) deeplink to this comment
Hi Scott,
No idea what's wrong. Can you send me your file (see address below)?
Comment by: dan.c.dugan@gmail.com (1-10-2010 16:19:11) deeplink to this comment
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 (2-10-2010 05:24:06) deeplink to this comment
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 15:32:00) deeplink to this comment
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 22:52:31) deeplink to this comment
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 (10-12-2010 12:39:04) deeplink to this comment
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 (10-12-2010 14:32:26) deeplink to this comment
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 (14-12-2010 19:02:07) deeplink to this comment
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 (15-12-2010 00:03:55) deeplink to this comment
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 (20-12-2010 12:17:27) deeplink to this comment
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 (20-12-2010 22:21:48) deeplink to this comment
Hi Justin,
Great, thanks for letting us know!
Comment by: Fabrizio (22-1-2011 03:39:09) deeplink to this comment
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 (22-1-2011 08:39:12) deeplink to this comment
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 (28-1-2011 09:51:46) deeplink to this comment
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 (28-1-2011 12:36:32) deeplink to this comment
Hi Mark,
I'm afraid you do need a separate Picture1, Picture2, .. name for each picture.
Comment by: Rich (17-2-2011 12:30:46) deeplink to this comment
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 (18-2-2011 01:53:46) deeplink to this comment
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 (20-2-2011 11:06:23) deeplink to this comment
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 (24-2-2011 06:54:34) deeplink to this comment
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 (24-2-2011 10:20:13) deeplink to this comment
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 (28-2-2011 22:27:09) deeplink to this comment
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 (1-3-2011 01:58:10) deeplink to this comment
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 (10-3-2011 17:04:54) deeplink to this comment
Will this method work if the cells are merged?
Comment by: Jan Karel Pieterse (13-3-2011 11:43:14) deeplink to this comment
Hi Yub,
I guess the only way to find out is by trying!
Comment by: Kelsey (18-3-2011 11:07:10) deeplink to this comment
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 (18-3-2011 11:57:10) deeplink to this comment
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 (1-4-2011 11:48:53) deeplink to this comment
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 (3-4-2011 05:10:44) deeplink to this comment
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 (18-5-2011 01:47:13) deeplink to this comment
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 (18-5-2011 02:01:25) deeplink to this comment
Hi Vivi,
Check that particular picture in the list of pictures; probably the picture is larger than it's underlying cell.
Comment by: Vivi (26-5-2011 17:57:15) deeplink to this comment
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 (27-5-2011 02:40:30) deeplink to this comment
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 (10-6-2011 04:33:51) deeplink to this comment
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 (13-6-2011 05:33:41) deeplink to this comment
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 (17-6-2011 02:55:43) deeplink to this comment
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 (17-6-2011 03:53:02) deeplink to this comment
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 (17-6-2011 04:50:15) deeplink to this comment
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 (18-6-2011 11:17:08) deeplink to this comment
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 (20-6-2011 08:12:05) deeplink to this comment
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 (21-6-2011 01:19:42) deeplink to this comment
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 (26-6-2011 17:08:54) deeplink to this comment
Thank you very much, this is exactly what i was looking for !
Comment by: Dianne (27-7-2011 11:55:24) deeplink to this comment
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 (22-8-2011 03:02:56) deeplink to this comment
Hi Dianne,
Can't you get it to work using the explanation on this page?
Comment by: Chandrashekhar (21-10-2011 23:42:42) deeplink to this comment
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 (24-10-2011 01:51:41) deeplink to this comment
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 (22-11-2011 04:15:13) deeplink to this comment
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 (22-11-2011 07:51:42) deeplink to this comment
Hi Milind,
I am not sure what you are trying to do, can you please try to explain?
Comment by: Andy (10-12-2011 09:59:53) deeplink to this comment
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 (11-12-2011 23:11:20) deeplink to this comment
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 06:20:23) deeplink to this comment
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 06:33:56) deeplink to this comment
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 13:16:00) deeplink to this comment
Thanks again - what's the best way to get it to you?
Comment by: Jan Karel Pieterse (13-12-2011 02:02:33) deeplink to this comment
Hi Andy,
You can send it by email, address at the bottom of this page.
Comment by: lim ck (2-1-2012 10:02:43) deeplink to this comment
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 (2-1-2012 22:15:38) deeplink to this comment
Hi Lim,
No, this example requires the picture to be placed on the worksheet.
Comment by: Yuda (15-1-2012 01:43:04) deeplink to this comment
Thanks very much... (^_^)
Comment by: Kylin (15-2-2012 01:17:35) deeplink to this comment
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 (15-2-2012 02:37:37) deeplink to this comment
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 (15-2-2012 04:14:31) deeplink to this comment
Thanks Jan. I will check with my workmate.
Comment by: Rob M (12-3-2012 15:30:01) deeplink to this comment
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 (12-3-2012 22:50:29) deeplink to this comment
Hi Rob,
Did you select the type "Bitmap image"?
Comment by: Andrew B (15-3-2012 15:24:48) deeplink to this comment
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 (23-3-2012 00:50:46) deeplink to this comment
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 (23-3-2012 03:56:35) deeplink to this comment
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 (5-4-2012 17:23:24) deeplink to this comment
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 (9-4-2012 10:09:14) deeplink to this comment
Hi Nico,
Are there multiple pictures on top of each other by any chance?
Comment by: b.ramakrishnan (12-6-2012 11:31:47) deeplink to this comment
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 (12-6-2012 14:04:35) deeplink to this comment
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 (12-6-2012 16:36:05) deeplink to this comment
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 (12-6-2012 16:38:11) deeplink to this comment
Hi,
Thanks for lettting me know.
Comment by: sandesh nilvarna (20-6-2012 08:40:33) deeplink to this comment
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 (20-6-2012 09:54:38) deeplink to this comment
Hi sandesh,
I am not sure what you mean?
Comment by: Jo Miller (19-7-2012 15:20:51) deeplink to this comment
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 (1-8-2012 06:46:25) deeplink to this comment
Like DAta validation,is there any way to select the logo from a drp down list.
Comment by: sandesh Nilvarna (3-8-2012 13:34:15) deeplink to this comment
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 (7-8-2012 11:01:25) deeplink to this comment
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. (26-8-2012 20:07:16) deeplink to this comment
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 (27-8-2012 14:40:41) deeplink to this comment
Hi Lynn,
If you like you can send me your current file (see address near bottom of this page).
Comment by: Jonathan R (30-8-2012 16:56:58) deeplink to this comment
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 (31-8-2012 11:33:14) deeplink to this comment
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 (29-9-2012 09:19:24) deeplink to this comment
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 (1-10-2012 10:24:37) deeplink to this comment
Hi Azeem,
Perhaps the picture is referring to too many cells?
Comment by: Azeem (1-10-2012 10:43:06) deeplink to this comment
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 (2-10-2012 09:23:55) deeplink to this comment
Hi Azeem,
Please email your workbook to me, address below...
Comment by: Sabzero786 (13-11-2012 09:44:35) deeplink to this comment
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 (13-11-2012 11:34:54) deeplink to this comment
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 (21-11-2012 15:30:54) deeplink to this comment
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 (22-11-2012 11:29:45) deeplink to this comment
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 (23-11-2012 13:31:01) deeplink to this comment
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 (23-11-2012 14:48:16) deeplink to this comment
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 (6-2-2013 08:35:28) deeplink to this comment
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 (7-2-2013 20:35:07) deeplink to this comment
Hi DK,
Please ask this question at http://www.eileenslounge.com
Comment by: Daniel Wilson (6-3-2013 16:03:45) deeplink to this comment
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 (18-4-2013 15:59:48) deeplink to this comment
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 (20-4-2013 21:16:49) deeplink to this comment
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 (5-1-2014 23:02:29) deeplink to this comment
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 (6-1-2014 07:05:16) deeplink to this comment
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 (7-3-2014 10:23:26) deeplink to this comment
Hi Vinod.
Feel free to contact me on:
dean.justlikethat@gmail.com
I have the solution to your problem
Thanks
Dean
Comment by: Chandra (16-7-2014 21:02:57) deeplink to this comment
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 (21-7-2014 09:38:31) deeplink to this comment
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 (21-7-2014 20:58:18) deeplink to this comment
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 (23-7-2014 16:28:29) deeplink to this comment
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 (12-8-2014 11:08:05) deeplink to this comment
Hi Phil,
Perhaps there is a problem with your range names?
Comment by: Chuck (14-10-2014 15:59:36) deeplink to this comment
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 (14-10-2014 17:14:09) deeplink to this comment
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 (14-10-2014 18:31:33) deeplink to this comment
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 (15-10-2014 07:51:15) deeplink to this comment
Hi Chuck,
Which Excel version are you using?
Comment by: Chuck (15-10-2014 14:19:09) deeplink to this comment
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 (15-10-2014 16:36:32) deeplink to this comment
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 (5-11-2014 08:28:33) deeplink to this comment
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 (10-11-2014 11:36:17) deeplink to this comment
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 (17-3-2015 14:22:25) deeplink to this comment
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 (17-3-2015 16:32:19) deeplink to this comment
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 (7-3-2017 22:25:16) deeplink to this comment
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 (8-3-2017 09:49:50) deeplink to this comment
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 (19-7-2017 13:52:26) deeplink to this comment
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 (13-8-2017 17:06:02) deeplink to this comment
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.
Comment by: Deepak (25-2-2018 06:10:07) deeplink to this comment
Hi,
I have created a ADD-ins and placed in excel ribbon using Custom UI Editor For Microsoft Office XML. When Click in Tab of this ADD-in it open a Excel DATA file and a Userform together, which take data that have been searched from this Excel file.
what i want is, Add-n should only open userform despite of open other excel data file, userform should take data from inbuilt workbook of ADD-in
i tried Application.isaddin true/false but it is not working!
When a workbook convert into addin by Application.isaddin true/false, no data can be taken from this hidden workbook of addin and userform is not taking data from this hidden workbook of ADDin
Comment by: Jan Karel Pieterse (26-2-2018 10:16:23) deeplink to this comment
Hi Deepak,
What happens when you click a custom ribbon button is set in the onaction callback of that button. The name of that onaction macro must be unique, otherwise you risk that the button may call an identically named macro in another workbook. So make sure your onaction callback has a unique name.
Comment by: DeePak (1-3-2018 06:46:24) deeplink to this comment
Hi,
I set addinn to open Excel Data file and userform together. Excel data file ( Named AD-details) has 1000 rows AD data which are being used by userform (Named: AD-search).
This userform takes data from this Workbook (AD-details) and showing result.
but as every add-inn has hidden workbook(sheets) and this 1000 rows data can be saved into it as well.
and i want when i click on onaction callback this must open AD-search Userform only and for its data, this userform should take data from workbook of Addinn itself
Comment by: Jan Karel Pieterse (1-3-2018 08:41:43) deeplink to this comment
Hi Deepak,
The reason your code does not work as expected is very hard to know without seeing your code. However, if your code addresses a different workbook than you want, make sure you inlude the workbook object reference in your code, e.g.:
instead of:
use:
Comment by: frank hens (21-9-2023 16:39:00) deeplink to this comment
here you link fixed images to a location i excekl sheet but what if you want to link dynamic data between excel and word 2010?
i query data using sql in the excel sheet and one of these data put in cel A1 are paths to an image like e.g. C:\voorbeeld beeldkoppeling\Etikett 12er MiniCard Vollmilch.jpg
this path should be linked to a word template where i want to display that pathwayfrom A1 from the excel file not as a pathway but as an image with always the same image format.
after updating the data and thus teh pathway to the imagein the excelfile the image should also dynamicallly be updated in the wordtemplate.
how do i do this ?
Comment by: Jan Karel Pieterse (22-9-2023 10:58:00) deeplink to this comment
Hi Frank,
As far as I know this can only be achieved using automation. Such as VBA code, Office Script, a .NET Com add-in, Office Javascript or Power Automate.
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.