Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Displaying Images based on a Result

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Displaying Images based on a Result.

Dave has a large database that he keeps in an Excel workbook. It consists of material samples and uses the VLOOKUP function to generate various forms and reports. Dave wants to include a bitmap image on the form that changes according to one of the variables. For instance, if the form is describing an apple, then Dave wants a picture of an apple to appear; if describing a pear, then a pear should appear; and so on.

This is certainly a challenging task, but it is one that can surprisingly be done without macros. The steps are involved, but not that difficult to perform once you get to it:

  1. On a new worksheet, select a cell (such as cell B4).
  2. Enter the name "apple" into the Name box. This defines the name "apple" to refer to cell B4.
  3. With cell B4 still selected, choose Insert | Picture | From File. Select the picture of the apple and insert it.
  4. Enlarge the width and height of cell B4 so that the picture of the apple is contained entirely within the cell.
  5. Repeat steps 1 through 4 for each of your other pictures, placing each picture in a different cell and naming them according to the contents of the picture. (For the sake of this example, I'll assume that "pear" is cell D4 and "orange" is cell F4.)
  6. On the worksheet that will contain your form, create a formula that will contain the names of the fruit, such as the following formula, which displays "apple," "pear," or "orange," depending on the value in cell G1. (It is important that the formula reference the names exactly as you defined them in step 2 for each fruit's picture. For the sake of this example, I'll assume that you entered this formula in cell A1 of Sheet1.)
  7.      =IF(G1=1,"apple",IF(G1=2,"pear",IF(G1=3,"orange","")))
    
  8. Choose Name from the Insert menu, then choose Define. Excel displays the Define Name dialog box. (See Figure 1.)
  9. Figure 1. The Define Name dialog box.

  10. Replace the contents of the Names In Workbook box with the word "Picture".
  11. Replace the contents of the Refers To box with the following formula:
  12.      =INDIRECT(Sheet1!$A$1)
    
  13. Click the OK button. You've now defined the name "Picture" to contain the formula entered in step 9.
  14. On the worksheet that will contain your form, select the cell where you want the dynamic image to appear.
  15. Choose Insert | Picture | From File. Select and insert a picture (it doesn't matter which one).
  16. Make sure the picture you inserted in step 12 is selected.
  17. In the formula bar, enter the formula =Picture. (This is the name you defined in steps 7 through 10.) The picture should change to reflect whatever fruit is named in cell A1.

Now, whenever the fruit name in cell A1 changes (which is, in turn, based on the value in cell G1), the image will change.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3128) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Displaying Images based on a Result.

Related Tips:

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Venugopal    10 Dec 2016, 01:32
Can i get a sample excel, somehow i am unable to execute the function, it is reflecting errorr
Mladen    26 Oct 2016, 08:00
ok, it is working quite ok but what if I need to have cell , where picture should appear, that will be automaticly filled up with picture according to the some other cell content?
for example, i have item number code.
and i want to program several cells with vlookup so some data related to the item no. i hvae will be take from other table ...but how i can automaticly copy pictures....using idea you describe(indirect) i need to write picture name in each cell and it take lots of time for often work ....
Callum    14 Oct 2016, 06:20
This worked perfectly for me thank you. My only problem is that I get a white border at the top and left around the image, I've tried playing around with the cells and the images but can't seem to get rid of it (I only notice it as the report I have the picture in has a black background. If there is no solution I can just make the report white

Thanks
Arpad    21 Sep 2016, 04:29
This is a miraculously hidden feature.
Thanks for the heck. Wonderful.
Macu    07 Sep 2016, 05:45
Hi,

If you want to remove the border,go to view and swith the gridlines off. It works, at least for me.

Anyway, what is missing in this manual is the fact that to have it working one needs to paste a randomly chosed empty cell by paste special: linked image (or sth like that, I've got the menu in Polish).

Cheers
Gustavo    11 May 2016, 19:55
Thank you!!! Master
KSzs    25 Mar 2016, 12:05
I just found this and it was useful. And for those who were complaining about the grey box: that are the borders around your cell. Just change the boarder color of your picture database and problem solved.
Hope that will help.
Kata
CammieJay    11 Nov 2015, 20:28
You, sir, are a genius.

Works beautifully in Excel 2016.
Surendra    21 Sep 2015, 09:17
Is this only valid for Picture not for Shapes ?
kadr leyn    10 Sep 2015, 13:02
Thank you very much sir,

I did a similar example :
" Calling Image From Another Page "

it's codes :

Resim1a =OFFSET(resim!$C$2;MATCH(Weather!$C$8;Liste;0)-1;0;1;1)
Resim1p =OFFSET(resim!$C$2;MATCH(Weather!$C$9;Liste;0)-1;0;1;1)
Resim2p =OFFSET(resim!$C$2;MATCH(Weather!$C$11;Liste;0)-1;0;1;1)
Resim2t =OFFSET(resim!$C$2;MATCH(Weather!$C$12;Liste;0)-1;0;1;1)
Resim3t =OFFSET(resim!$C$2;MATCH(Weather!$C$13;Liste;0)-1;0;1;1)


You can view descriptions and download template here :http://merkez-ihayat.blogspot.com.tr/2015/09/calling-image-from-another-page-with.html
Nick    10 Sep 2015, 11:41
" Tapa 16 Jun 2015, 17:47
Thank-you so much for sharing this. It worked for me. However, I also got a grey border around the image. It gets away if I set soft edges to 1 point, but this also hides the image by 1 point as there is no extra space in my image. So, I cannot set soft edges to 1. Any idea if/how I can remove the border?"

Tampa - The grey border is the result of the formatting around the selected "source" cells. So where you have stored the pictures of all the oranges, apples, etc. You must remove all borders around these cells and set the fill color of the source cells AND adjacent cells to white. Once done the dynamic image will be the picture without any border. If you like you can then select the dynamic photo and select the "format picture" tab to add a border around the entire image.

-Best
Xander    01 Sep 2015, 10:18
Works great! Thanks
BKF    18 Aug 2015, 18:37
This is one of the most poorly written procedures I've ever seen. The instructions are all over the place. Very confusing.
Pramod Sinha    01 Aug 2015, 04:37
Could anybody help me out the complete procedure of preparing ID Card data sheet with required formulae. I shall be so thankful as I am very much in need of such a software prepared on excel.
Tapa    16 Jun 2015, 17:47
Thank-you so much for sharing this. It worked for me. However, I also got a grey border around the image. It gets away if I set soft edges to 1 point, but this also hides the image by 1 point as there is no extra space in my image. So, I cannot set soft edges to 1. Any idea if/how I can remove the border?
Emz    31 May 2015, 22:39
I need help to make my work easy using Excel. Whenever I type the item number, the data shows up with the help of Vlookup formula.

I would like to have the picture of the item added as well. I have hundreds of pictures and the sizes are not the same. It needs to be compressed without being distorted, something like being relevant to the original size.

Whenever I type the item name b2, the picture should be inserted in b3. The next item would be on f2, the picture should be inserted in f3, and so on.


I hope someone here can really help me out.
Josh    21 May 2015, 12:23
Also if found it easier to simply go into the Name Manager and create a Name for what you want the picture to be called and then under "refers to" click on where you have the picture stored. Then create name of "Pictures"(like in the example) and this refers to where you are storing the INDIRECT formula
Josh    21 May 2015, 11:56
Works great!

It did though require some reworking. Here are some tips that helped me:

1. In step 2, it took me awhile but I found the name box was where the cell reference is(the box in the upper left corner of the spreadsheet). You need to change this to whatever you want to call the picture

2. In the last step, where I mainly got the error, I came to find out that the INDIRECT formula in step 9 must reference where you are putting the names of the pictures. So let’s say you, like in the example, are dealing with fruit and you have you have the name of apple in b15 and want the picture in c15. The in the INDIRECT formula you will change it to = INDIRECT(Sheet1!b15). Then you paste any picture in c15 and while it’s selected enter =Picture. This should work. I was getting the “Reference is not valid” error and it was because I wasn’t referencing the correct cell. Also as noted the name in the cell you are referencing must match what you named the picture.

Also one thing that's annoying is that you will have to create a new "Name" value for each picture you insert, assuming you want them in different cells. So if you want one in c15 and c16, you need two "Picture" Names
glg    01 May 2015, 09:37
Doesn't work. I find the instructions cryptic. I get the same error as others, 'Reference is not valid'. This was very frustrating. jmathane's solve didn't work either. Big waste of time for me.
Neil Sweeny    30 Nov 2014, 18:22
Hello,
Can you help me please. I am doing a spreadsheet for my school work.

I want to trigger a set of text messages in a cell based on values in another cell.

There are 5 one word text messages which will appear in the target cell

The values in the main cell will all have ranges e.g. 2-5, 6-13, 14-17, 18-25, 26 +

What formula should I use?

Thank you.
agung    17 Oct 2014, 00:15
thankyou sir,, it works
Leks Inans    25 Sep 2014, 10:12
Works like a charm
James Koch    27 Aug 2014, 06:32
Very Cool, i couldn't get it to work at first but now that it does its very cool, i could have used this so many times before.
Luis    13 Aug 2014, 14:02
I cannot enter "=Picture" to the formula bar as I keep getting a 'Reference is not valid' message. I tried jmathane's solution but had no success (using Excel 2010). Has anyone been able to solve this problem? Thanks
Dean    12 Aug 2014, 14:07
Awesome!

I agree that use the advice of jmathane and insert as a picture from the developer tab so you can see the formula.

Also, you don't have to define names for each picture. You can just make it say something like "Sheet1!A1" instead of using "apple".
Mohinder    31 Jul 2014, 18:30
I want to use this trick in Excel 2013. Can you help please?
I have Date1 and Date2. In a cell, I want to show Green Dot if Date1 is greater than Date2, Yellow Dot if both dates are equal, and Red Dot if Date1 is smaller than Date2.
Raj    12 Jul 2014, 15:51
Allen Wyatt: thank you very much, it work fine, and i'm happy to learn new things ..
Thanks again
Rajroshan I    16 Jun 2014, 07:39
Dear Sir,

 I want to know how below example works

Here number of work orders are there with Name and Month, for example:

Work order Name Month

1236 ADV Jan-13
1456 RGE Feb-13
8597 REHD March-13
9567 TYU Apr-13
8649 IUR MAY-14


Suppose if entering work order in one row Name and month should display if that work order is available in the sheet orl's it should display -NA-

Ex: If i am entering..

Work order: 8649

Output should be:

Name: IUR Month: MAY-14

         or

Work order: 10010

Output should be

Name: -NA- Month: -NA-

kindly reply as soon as possible.

regards,
Rajroshan.I
 
larry trimboli    26 May 2014, 17:17
this works very well---thank you--- but i do not understand the "indirect" function...it seems like voodoo on how it pics up the pictures...how does this accomplish this....a better understanding of the function would make it allot easier to use on a regular basis
Chris    23 Apr 2014, 15:13
Great work Allen. I got it to work after 2-3 tries. The directions need to be polished up a bit, but overall I was able to accomplish my task. Thanks!
allanc    08 Feb 2014, 19:48
I too have made this work, and it is an interesting exercise.
In reply to Joe, I think it is not an image border you are seeing, but the border around the cell which contains the image. If you turn off gridlines, your image should no longer have a border.
I am also finding that when this works, other aspects of Excel don't. Specifically, cell comments display for a split second then disappear. I'd be keen to understand why this is the case.
Robert    08 Feb 2014, 07:29
When I follow this tip the image is always in a square box. Is there any simple way to have the image appear without a surrounding white background? I am trying to create a map of interlocking hexagons so would need the dynamic image created to be hexagon rather than square.
Joe    03 Feb 2014, 12:44
Ok, I answered my own question.

You have to right click and select Format Picture.

The only thing I could find that would alter the border was the Glow and Soft Edges.

Setting the Soft Edge to 1pt seems to work well.

Joe
Joe    03 Feb 2014, 12:13
I found that my named ref had to include the sheet name also.

=INDIRECT(CONCATENATE("Sheet1!",Sheet1!$A$1))



I also noticed that when you click on the image and put in the named ref, is switches the picture to kinda like a Text Box with the picture inside of it.

This creates a BORDER around all of it. How do we turn off the gray border?
Shunt    17 Jan 2014, 15:06
I tried this and it broke the rest of my VBA code. For those who had the same problem, you might have better luck putting all of the pictures on top of each other and editing their .visible property based on the control cell's value. It may not be the most elegant way, but it worked for me. Here is some of my Excel 2010 VBA code to get you started:

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address
       Case "$B$12"
          If Range("$B$12").Value = Range("$S$2").Value Then
                Shapes("Picture 17").Visible = True
          Else
                Shapes("Picture 17").Visible = False
          End If
          If Range("$B$12").Value = Range("$S$3").Value Then
                Shapes("Picture 11").Visible = True
          Else
                Shapes("Picture 11").Visible = False
          End If
          If Range("$B$12").Value = Range("$S$4").Value Then
                Shapes("Picture 12").Visible = True
          Else
                Shapes("Picture 12").Visible = False
          End If
       Case Else
    End Select
End Sub

Good luck,
Shunt
Chris    16 Jan 2014, 16:59
Hi guys,

I have followed the instructions and I got it working, really happy. It would be life-saving if someone could look at this situation:

The workbook where the picture is updating also contains a number of cells with values selected from drop-down lists. After the modifications to have the picture updated, the drop-down lists are not shown anymore. You click on the cell and the arrow indicating the drop-down list shows up for a split of the second only, then dissapears. The actual list is not displayed. You can still type the value in the cell.

This happens right after the step where I select the picture and then type '=Picture' in the formula bar, which is step 14. The "picture updating" feature starts to work but I loose the drop-down lists, as described above.

All objects are visible, I have checked this. I am using Excel 2013.

Any reply will be much appreciated.
Art Lichtenberger    05 Jan 2014, 09:44
I am trying to create an info {name, email, group} and pic directory for everyone who uses my lab (>50 people) where I print it off on 18x24 paper... I want to have two column sets of this info laid out on the page (sorted by last name)... so you would start reading the names/info in the first column, read down to the bottom, then start reading at the top right column etc. I have a different/source sheet with all this info in one column-set including pics for each person/row. I can use Vlookup to create the 2column-set directory, but it doesn't work for pics. Your method seems a bit intractable, given that I have over 50 pics, and they change every semester. Is there an alternative approach for this?
gamondeluxe    25 Dec 2013, 08:35
For those who could not find way to access formula bar while pic being selected, just follow jmathane's instruction. It works great. You may wanna save your workbook to make it works.
Arlindo Campos    22 Nov 2013, 23:11
Solving Step 14

In order to be able to enter the formula(=Picture) on step 14 try first to go to G1 and input any of the pictures value (1,2 or 3) then select the picture and enter the formula(=Picture) on the formula bar. When you input a number on G1 the correspondent picture gets associated to Picture(defined on step 7). If there's no picture associated at the time you try to input the formula the system considers that there's no referenced picture and wont allow you to input the formula.

Another tip:

The formula on A1 =IF(G1=1,"apple",IF(G1=2,"pear",IF(G1=3,"orange",""))) is not really needed. If you just type "apple", "pear" or orange on A1 it will work just fine.

I hope it helps, it is working great for me!
If you want to
Phil    30 Oct 2013, 08:20
Your instructions get unclear at Step 9. This would have me enter the word "Picture" in the blank box immediately following "Names in workbook:", modify the formula in the "Refers to:" box to "=INDIRECT(Sheet1!$A$1)," and then click OK.

This results in the word "Picture" being added to the list of names, and later results in an image of the word itself (not the image).

Also, you say, "insert a picture (it doesn't matter which one)," and immediately thereafter say "make sure the picture you inserted in step 12 is selected." OK - it doesn't matter, but it does matter.

And where the hell is step 12?

I hope no one's paying for this advice.
Mohamed    03 Sep 2013, 18:00
Hi Julien B,

This is probably very late, but yet again, it might be useful for someone out there:

You could delete the picture link at the start of your code using

  Worksheets("Sheet_Name").Shapes("Picture_Name").DrawingObject.Formula = ""
or
  Worksheets("Sheet_Name").Shapes.Range(Array("Picture1","Picture2","Picture3")).DrawingObject.Formula = ""
  [if you have many pictures with the same link]

and resetting it at then end using

  worksheets("Sheet_Name").Shapes("Picture_Name").DrawingObject.Formula = "=Picture"

[here Picture_Name refers to the dynamic picture that is actually changing]


Alternatively, if you have many (different) picture links then you could try setting the calculation mode to 'manual' at the beginning of your vba code

  Application.Calculation = xlCalculationManual

and then resetting it to 'automatic' at the end

  Application.Calculation =xlCalculationAutomatic

. However, if your vba code depends on calculated formulae, then you need manually calculate the worksheet/workbook at the appropriate locations in your code using
  Application.Calculate
or
  Worksheets("Sheet_Name").Calculate

All the best
jmathane    01 May 2013, 10:52
Hi folks, I finally found the solution : In the Developer bar, chose the menu Insert > Active X Controls > Image (chose the corresponding icon). Place and size the image field on your sheet. You can attach the formula "=Picture" and it works !!
Alex Quinn    28 Apr 2013, 11:09
Works for me! (Excel 2013, Win7)

The picture is floating, not attached to a cell, but which picture it shows is indeed controlled by the formula. The instructions are perfect.
Mateo    27 Mar 2013, 17:11
Seems Im having the same problem as J Winters. That last step just not working for me in Excel 2007. Clearer instructions and any help highly appreciated.
Julien B    27 Mar 2013, 08:41
Hi,

I also used this technic in some of my files.

However, some some reasons I'm not aware of, it ruins the speed of the VBA code of all the file...

My VBA code, which doesn't manipulate at all these images, takes ages to complete.

When I supress the conditional images, it comes back to normal (a few seconds).

Has anyone a clue on this issue?

Thanks in advance
Diogo Borges    14 Mar 2013, 08:43
amazing stuff! thank you very much!
Charles Schroeder    18 Jan 2013, 05:24
I now want to add the same images in a different cell to be triggered by the value in another cell. I tried doing it using the same image but then nothing works. I tried to duplicate the images with a different name i.e. pear now becomes pear2, etc and also defined the name of cell A2 as Picture2, and in the Formula bar enter =Picture2. The second image still does not work. What have I done wrong!
Charles
J. Winters    15 Jan 2013, 03:39
This does not work in Excel 2007.

It will fail on the last step since you cannot select a picture and enter a formula in the formula bar. The picture is float on top of the worksheet.

Perhaps this worked differently in previous versions of excel?
Charles Schroeder    11 Jan 2013, 15:38
This is so simply and it works. I must admit that I had to do it a few times to get it right. Thanks a million.
My tip is to print the instructions and then to do it slowly step by step. Regards Charles (in Cape Town)
MirU    05 Nov 2012, 04:27
This is exactly what i was looking for. Many thanks!
Changing pictures without having to fall back on VBA..
JimBob    23 Aug 2012, 13:46
This is just what I was looking for, thanks! And fyi, the numbering in your document appears to reset for the different sections, i.e. your step for your formula for apple/pear/orange resets, so later on when you refer to "Step 12" it's actually the 3nd step 2 :)
Phil    24 Jul 2012, 05:07
It appears you need to refresh the formula (=picture) to get it to show a changed image.
Gloria    11 May 2012, 09:18
In the last step, I can access the formula bar with the picture selected. What am I missing?
Joe    14 Apr 2012, 09:23
Like John, I am also trying with a larger (30) number of pictures. I am trying to make a NHL Playoff pool spreadsheet, and I would like to display the logos based on which team is selected. My challenge is also that I will need this funtionality throughout the spreadsheet, so a dynamic method would be preferred. Any ideas?
John    13 Apr 2012, 15:03
What if you are wanting to do this on a much bigger level? I have this function creating a link to the image: =HYPERLINK(VLOOKUP(B1,Image_Names!$A$1:$B$200000,2,FALSE) where when the item entered in cell B1 is found in column A of sheet "Image_Names", a hyperlink is made of the filename that is in column B. What I want to do is have the image shown in the Excel file like above, only with thousands of images it would not be practical to name them individually for the method above. Any ideas on how to display based on the hyperlink?
T. Chan    11 Mar 2012, 11:26
Step 1-4 can't bind the image to a cell. The image is "float" on the spreadsheet.
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.