This post may contain affiliate links. Please read my disclosure for more info.

3 ways to change or insert images based cell values

Change image based on cell value

From stock parts to holiday villas, from employees to logos, there are plenty of reasons to insert and automatically change an image based on a cell value.  Or you may think if this a formula which changes a picture, rather than a number.  Ultimately, the purpose is to link an image or picture to a cell.

In this post, we will use a basic example of changing a picture of a country’s flag whenever the text within a cell changes,  We’ll explore 3 different methods to achieve this task:

Each solution has it’s own strengths and weaknesses; pick the one which best meets your requirements.

Let’s look at each of these methods in turn.

Change image with a named range + INDEX/MATCH + linked picture

We’ll start the first example with a list of countries in Column A and their relevant flags in Column B.

Image lookup - Country list

Next, we’ll create a named range by selecting Formulas -> Define Name.

Image lookup - Define Name

In the New Name window create a new named range called “CountryLookup”.

Image lookup - new name

This named range will refer to an INDEX/MATCH function containing the country name and the flags.

=INDEX(Sheet1!$B$2:$B$15,MATCH(Sheet1!$F$2,Sheet1!$A$2:$A$15,0))

In this formula:

  • Cells B2-B15 contain the cells with the flags
  • Cell F2 contains the name of the country to be found
  • Cells A2-A15 contains the names of the countries

After the named range has been created, select any cell which contains a flag, click Home -> Copy (or Ctrl + C) to copy the cell, then select a different cell, click Home -> Paste -> Linked Picture (alternatively, the Camera Tool is an option).

Image lookup - Paste Linked Image

The pasted image will appear.  Select the image, then change the address in the formula bar to be equal to the named range created earlier (“CountryLookup” in our example).

Image lookup - Linked image address

For completeness, Cell F2 has been changed into a Data Validation drop down containing all the countries.

Each time a new country is selected from the drop down the image will change automatically.

Image Lookup - Index Match GIF

The key points to remember for the named range + INDEX/MATCH + linked picture method are:

  • The INDEX/MATCH function is inserted into a named range.
  • The INDEX/MATCH function will return the cell address containing the image.
  • A linked shape is created, which uses the named range as it’s source.

The “Reference isn’t valid” Error

Reference Isn't Valid

If you recieve the “Reference isn’t valid” error there are two likley problems:

  1. the result of the INDEX/MATCH function produces an error.  For example, trying to display a picture of the flag for “USA”, when the value is Column A is “United States of America”.
  2. The named range applied to the picture is not a valid named range.

INDEX/MATCH is a formula combination, which can achieve some amazing things, picture formulas are just one one of those amazing things.

Change image with chart fill + #N/A

The second method uses a stacked column chart to hold the images.

Columns A contains the names of countries, Column B contains an IF/VLOOKUP formula.

Image Lookup - Chart NA values

The formula in Cell B2 is:

=IF(VLOOKUP(A2,$F$2,1,0)=A2,1,NA())

The purpose of the formula is to show 1 if Cell F2 is the same as the country in Column A, else #N/A is displayed.  The formula has been copied down to the last country name in the list.

Select all the values in Column A and Column B.

From Ribbon click Insert -> Charts -> Stacked Column.

Image Lookup - Chart Stacked Column

A chart will appear.  Right-click on the series, then click Select Data…

Image Lookup - New Chart

From the Select Data Source window click the Switch Row/Column button, the Legend Entries box on the left should contain the list of countries as different chart series.

Then click OK.

Image Lookup - Switch RowColumn

Now, it is all about formatting:

  • Set the maximum Y-axis to 1
  • Delete the Y-axis
  • Delete the Chart Title
  • Delete the X-axis
  • Format the Data Series to change the Gap Width to be 0%
  • Re-position and resize the chart to fit your requirements

Select each country in turn and format the visible data series so the fill for each bar is a picture of the country’s flag.  Inserting images one at a time, in this way can be time-consuming.  The macro below will insert the images into the chart fill automatically.  The names of the countries must exactly match the names of the images.

To run the macro, change “Chart  1” to the name of your chart, and set the file path of the images (including the image type file extension).  Select the cells containing the country names and run the macro.  The chart should automatically populate with the images.

Sub InsertPicturesIntoChart()

Dim i As Integer
Dim selectedCells As Range
Dim imageFullName As String

'Select the cells with the country names before running macro
For Each selectedCells In Selection
 
    i = i + 1

    'imageFullName is the file path to the image.  
    'Country name in Column A must match the name of the image.
    imageFullName = "C:\Users\marks\Documents\Flags\" & Cells(i + 1, 1).Value & ".png"

    'Change the fill of the chart Series.  Change Chart 1 to the name of the chart.
    ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(i).Format.Fill.UserPicture imageFullName

Next selectedCells

End Sub

The fully working chart achieves a similar result to the first example.

Image Lookup - Chart GIF

The key point with this method is:

  • When #N/A is used in the chart source the value is not displayed, only the matching value will be displayed.
  • The image is used as the fill of the stacked column chart.

Change image with a VBA User-Defined Function

Finally, we will consider a VBA user-defined function.  This is a formula created using VBA macros to insert and change the picture.

The images must be saved separately within a single folder, and each file name must match exactly with the name of the country.  Copy the following code into a standard module within the Visual Basic Editor.

Public Function PictureLookup(Value As String, Location As Range)

Application.Volatile

Dim lookupPicture As Shape
Dim sheetName As String
Dim picTop As Double
Dim picLeft As Double

sheetName = Location.Parent.Name

'Delete current picture if exists
For Each lookupPicture In Sheets(sheetName).Shapes
    If lookupPicture.Name = "PictureLookup" Then
        lookupPicture.Delete
    End If
Next lookupPicture

'Get position of cell calling the UDF
picTop = Location.Top
picLeft = Location.Left

'Add the picture in the right location
Set lookupPicture = Sheets(sheetName).Shapes.AddPicture _
("C:\Users\marks\Documents\Flags\" & Value & ".png", msoFalse, msoTrue, picLeft, picTop, -1, -1)

'change the picture name
lookupPicture.Name = "PictureLookup"

PictureLookup = ""

End Function

This function can be used on the worksheet as follows:

=PictureLookup(A2,B2)

Cell A2 contains the name of the country and Cell B2 is the location to place the image.

Look at this function in action.  It behaves in a similar way to the previous examples.

Image Lookup VBA GIF

If you wish to amend the VBA to change the images further, such as size, rotation etc, you can find further guidance here.

Using multiple images on the same worksheet

If you have multiple pictures and formulas on the same sheet, the following macro will enable this:

Public Function PictureLookup(Value As String, Location As Range, Index As Integer)

Application.Volatile

Dim lookupPicture As Shape
Dim sheetName As String
Dim picTop As Double
Dim picLeft As Double

sheetName = Location.Parent.Name

'Delete current picture with the same Index if exists
For Each lookupPicture In Sheets(sheetName).Shapes
    If lookupPicture.Name = "PictureLookup" & Index Then
        lookupPicture.Delete
    End If
Next lookupPicture

'Get position of cell calling the UDF
picTop = Location.Top
picLeft = Location.Left

'Add the picture in the right location
Set lookupPicture = Sheets(sheetName).Shapes.AddPicture _
("C:\Users\marks\Documents\Flags\" & Value & ".png", msoFalse, msoTrue, picLeft, picTop, -1, -1)

'change the picture name
lookupPicture.Name = "PictureLookup" & Index

PictureLookup = ""

End Function

This function can be used on the worksheet:

=picturelookup(B5,H5,1)

The key difference to the original function is the inclusion of a unique counter (highlighted in red above).  Each picture must have it’s own unique counter for the function to work correctly.  Possible ways to achieve this are:

  • Manually changing the function for each instance (manually changed to 2 in the example below)
    =picturelookup(B5,H5,2)
  • Referencing a cell which contains a unique number (C2 contains the unique counter)
    =picturelookup(B5,H5,C2)
  • Using the ROW() function (where the function is not repeated across the columns the ROW() number will be unique)
    =picturelookup(B5,H5,ROW())

Advantages & disadvantages of each option

Named range + INDEX/MATCH + linked picture

  • Advantage: All the images are contained on the worksheet
  • Disadvantage: Could accidentally move images outside of the cell range

Chart fill + #N/A

  • Advantage: Images contained within the chart, so difficult to change
  • Disadvantage: Time consuming to set-up
  • Disadvantage: Requires a helper column to function correctly

VBA User Defined Function

  • Advantage: No additional columns required on the worksheet
  • Disadvantage: Requires users ability to use VBA
  • Disadvantage: Image name, must be the same as the value on the worksheet

69 thoughts on “3 ways to change or insert images based cell values

  1. Joe says:

    Hey, i’m trying to implement the VBA solution to my excel file but i keep getting a #VALUE! I changed the location to the proper location where i have my images and made sure they were saved as .png images but it still doesn’t work. Do I need to change anything else in the code?

    Thank you, joe.

    • Excel Off The Grid says:

      Hi Joe,

      You shouldn’t need to change anything else in the code.

      It can be quite difficult to diagnose problems with User Defined Functions. I think the most likely cause will be an incorrect file path or file name.

      Insert the following code after Application.Volatile:
      Debug.Print “C:\Users\marks\Documents\Flags\” & Value & “.png”

      Change the file path for the one you are using. Then calculate the worksheet.

      Look at the Immediate Window (Ctrl + G if it’s not visible) in the Visual Basic Editor and confirm that the file path and file name exactly match with the location of the name you’re expecting.

      If that doesn’t help, e-mail the file to me, I will have a look.

      • Joe says:

        Thank you for the quick reply! I was actually able to figure it out and it was the file path, I forgot tho add a \ to the end of the path and that was the issue haha. Thank you for offering to help though! Also, thanks for writing this post, thiis helped me out a lot!
        Thank you, joe

      • Ted says:

        It seems I am also having an issue. If I copy paste =PictureLookup(A2,B2) I get #VALUE!… When editing the cell directly, it gives me a syntax issue. I can fix it by using =_PictureLookup(A2,B2) but then it returns #NAME?

          • Excel Off The Grid says:

            Hi Ted,

            I send you an e-mail response earlier today. I believe the two issues in your file are:
            (1) User Defined Functions must be included in a Module, rather than in the ThisWorkbook or a Sheet
            (2) The Function name must be unique (i.e. not the same as a Sub or another Function).

            Please let me know if that fixes the issue.

  2. Neil says:

    Hi, I went for the first option and got it working fine. Thanks!

    Say I have a spreadsheet where I want the same functionality 30 times… all looking at the same drop down list/pics. Do I have to create 30 rules? Or is there a slicker way of doing it? An example might be a list of 30 kids – I choose their favorite fruit next to each child, and want picture of the fruit next to each one. Thanks.

    • Excel Off The Grid says:

      Hi Neil,

      Unfortunately, the named range is applied to the picture and not a cell, therefore it is not possible to apply a relative cell reference. Which means by using the INDEX/MATCH method it would be necessary to create 30 named ranges and 30 pictures.

      With the VBA User Defined function it would be possible, as it behaves like a standard Excel formula.

  3. Peter says:

    Hello.
    Solution 3 works very well. Thank you!
    However, it seems that it can only display one picture.
    For example, if I use the function “=PictureLookup(E2,C2)”, I get the picture on C2.
    But if I add another line “=PictureLookup(E3,C3)”, I can’t get a second picture (until I delete the previous “=PictureLookup(E2,C2)” formula).

    Do you know if there is a solution to re-use the PictureLookup function (to display more than one picture)?

    Thanks!

    • Peter says:

      I think I found a solution:
      in the VBA Code (solution 3), delete the line lookupPicture.Delete

      This is great!!! Thanks again for the VBA solution

  4. LM says:

    Hi,
    Great article, thanks!
    I am trying the first option(the only one I basically understand).
    On my first worksheet, I have a dropdown list linked to a list of bottle names on a second worksheet. On this second worksheet, the bottles names are on the same rows as their pictures. I want the picture of the bottle selected with the dropdown list to appear dynamically on the first worksheet.
    Everything works fine until I get to the step where I have to select the pasted image and change the address in the formula bar to be equal to the named range created before.
    Excel then tells me that the “Reference is not valid”.
    Would you happen to know what could cause this and how I could fix it?
    Thank you!

    • Excel Off The Grid says:

      Hi LM,

      This occurs where the text entered into the formula bar is not equivalent to a valid cell address. The possible fixes are:
      – Check there is an = sign in the formula bar at the start of the Named Range
      – Check the spelling of the Named Range is correct
      – When creating the Named Range is the INDEX/MATCH set-up correctly and looking at the correct sheets?

      One of these should be the problem. Let me know how you get on.

  5. LM says:

    Hi!

    Thank you for your answer!

    I think that the problem is in the set-up of the formula for the named range, but I cannot find where it is. The formula for the named range is as follow:
    =INDEX(Table1[Picture];MATCH(‘Product Dashboard’!$D$2;Table1[Bottle Name];0))

    (I used “;” instead of “,”as in your example because excel wouldn’t accept the formula without it. If I understand correctly this comes with Excel 2013)

    Do you see anything wrong with it?

    Would you be ok to have a quick look at the file itself?
    I’m pretty sure that the mistake is obvious for someone who understand a little bit more excel.

    Thank you.

    • Excel Off The Grid says:

      I think I’ve solved it.

      The issue is with the single quotation marks surrounding the ‘Product Dashboard’ within the INDEX/MATCH function.

      They have been converted into a different type of quotation mark. Try deleting them and re-typing them. When I did that I managed to get it to work.

      Let me know if that solves it for you too.

      • Ashar Masood says:

        I am Having a similar Problem. But it cant seem to just go away…

        See if you can spot and help me out

        =INDEX(Price_List!$B$2:$C$11,MATCH(Master!$I$10,Price_List!$B$2:$D$11,0))

        • Excel Off The Grid says:

          Hi Ashar,

          From looking at your formula, I suspect the issue is with the two ranges provided.

          Are the text/values are in Column B and the images in Column C?

          The MATCH function needs to have one column, therefore
          $B$2:$D$11 would need to become $B$2:$B$11 (these cells contain the text/values).

          In this circumstance, the INDEX function also needs one column, therefore $B$2:$C$11 would need to become $C$2:$C$11 (the cells containing the images).

          • Ashar Masood says:

            You mean to say MATCH Works with One Column at a time Right.

            So The resulting formula should be:

            =INDEX(Price_List!$B$2:$B$11,MATCH(Master!$I$10,Price_List!$C$2:$C$11,0))

            As Column B on Price List sheet has the Images and the C Columns Have the matching codes.

            Actually i figured this out but now m stuck on another thing its giving me a “Reference isn’t valid when i try to assign the defined name to the image copied.

            BTW, i copied the cell not the image 🙂

            Why does this error occur as i have checked it all over the net and couldn’t find much specific to this.

            Thanks for all the help.

            Regards,

      • MB says:

        I went for method 1, and having the same problem. The formulas are right. I have checked again and again. But the pasted linked image will not take the defined name as its formula. Using 2016 version. Can you help?

        • Excel Off The Grid says:

          Hi MB – having looked at your file it’s because the reference number in the table is formatted as text, whilst the lookup number is formatted as a number.

          Once they are book text or both numbers it will work.

  6. Kirsten says:

    Hey there, I am using the VBA option to have a picture populate from a file with pictures that match the value I am putting in. I only changed the location name for the file containing pics in the formula above, and have verified that they match the value I type in to get the picture to pop up in my selected cell. However, I am getting a #NAME error when trying to use the function in excel. Keep in mind I am a total noob :). I had to google how to create a module. Any ideas what I have done wrong? I can send screenshots.

    • Excel Off The Grid says:

      Hi Kirsten,

      The most likely issues to check are:
      (1) Is there a backslash ( \ ) at the end of file path?
      (2) Is the file type of the image a “.png”, if not you will need to change the code to include the file type you are using.

      If these still don’t fix your issue let me know.

  7. mohamed says:

    Hi,
    Thank you for this great article.
    i want some help to locate the picture at the center of the cell using the 3rd method vba.
    Thank You!

    • Excel Off The Grid says:

      Hi Mohamed,

      Find the following line:

      Set lookupPicture = Sheets(sheetName).Shapes.AddPicture _
      ("C:\Users\marks\Documents\Flags\" & Value & ".png", msoFalse, msoTrue, picLeft, picTop, -1, -1)

      Add this code below it.

      lookupPicture.Top = Location.Top + (Location.Height / 2) - (lookupPicture.Height / 2)
      
      lookupPicture.Left = Location.Left + (Location.Width / 2) - (lookupPicture.Width / 2)

      This will center the image in the Cell.

      Thanks,

      Mark

  8. Roberta Zaviolo says:

    Hi! Everytime I press the “delete button” without any cell selected, the imagens reapear, one above other. How can I stop it?

        • Excel Off The Grid says:

          I’ve not been able to recreate your exact issue. The images will appear and disappear every time the worksheet recalculates.

          You could try removing the following line of code.
          Application.Volatile

          But I’m not sure I’ve answered your question as I’ve not been able to recreate the issue.

  9. Ryleonardo says:

    Hello Exceloffthegrid,
    Thanks for this wonderful guidance! I find the code for “Using multiple images on the same worksheet” very useful.
    However, could you please also kindly advise on how the formula can be modified such that the images can be 1) rotated a certain number of degrees, 2) flipped horizontally, 3) set as background or foreground, and 4) increased in size according to the values of certain reference cells?
    Thank you very much! I hope it is not too much.

    • Excel Off The Grid says:

      Hi Ryleonardo,

      I’m not certain what you mean by set as background or foreground. But the other items could be solved as follows:

      Function:

      Public Function PictureLookup(Value As String, Location As Range, _
      Index As Integer, RotateBy As Integer, Flip As Boolean,  _
      resizePct As Double)
      

      Insert the following code before PictureLookup = “”

      'Rotate image
      lookupPicture.Rotation = RotateBy
      
      'Flip image horizontally
      If Flip = True Then lookupPicture.Flip msoFlipHorizontal
      
      'change image size
      lookupPicture.LockAspectRatio = msoTrue
      lookupPicture.Width = lookupPicture.Width * resizePct
      
      • isaac chehebar says:

        Hello, this code is truly amazing! thank you for your brilliant work.

        Can you please advise how to use the last part of the string?

        ‘change image size
        lookupPicture.LockAspectRatio = msoTrue
        lookupPicture.Width = lookupPicture.Width * resizePct

        I would like to shrink the image to fit inside the cell, with at least 5% margin room on all 4 sides. I was able to center the image based on a tip provided above. Basically, if the cell height is 100 pixels, i want the height to be a total of 90 pixels. (5 pixels away from top and 5 p from the bottom). Do i need to put a number instead of “resizePct”?

        • Excel Off The Grid says:

          Hi Isaac,

          I suggest you try to read and understand the whole conversation with Ryleonardo (above and below), as it has all the answers you need.

          Yes, you could replace:
          lookupPicture.Width = lookupPicture.Width * resizePct
          with
          lookupPicture.Width = lookupPicture.Width * .9

          You will want to apply the code to center the image in the cell after you resize the image.

          • isaac chehebar says:

            Thank you so much!
            needed to just change one thing:
            lookupPicture.Width = locationPicture.Width * .9

            You are amazing!

  10. Ryleonardo says:

    Dear Excel Off The Grid,
    Thanks very much for your help. Your response works amazingly well.
    To clarify, I am trying to combine certain pictures. When an overlap occurs, excel automatically treats the earlier inserted pictures as background of the latter ones. What I would like to request is a code to instruct by reference cell value that a picture should be a foreground instead of having to repeatedly “right click”, “bring to front”.
    As an aside, could you please add a code to invert (i.e. flip vertically)?
    Thank you!

    • Excel Off The Grid says:

      Try adding the following:

      Function:

      Public Function PictureLookup(Value As String, Location As Range, _
      Index As Integer, RotateBy As Integer, HFlip As Boolean, _
      VFlip As Boolean, resizePct As Double, ZPosition As Integer)
      

      VBA Code to insert above PictureLookup = “”

      'Force z-order to zero then bring forward
      lookupPicture.ZOrder msoSendToBack
      If ZPosition < = Sheets(sheetName).Shapes.Count Then
          Do While lookupPicture.ZOrderPosition < ZPosition
              lookupPicture.ZOrder msoBringForward
          Loop
      End If
      

      Remove this:

      'Flip image horizontally
      If Flip = True Then lookupPicture.Flip msoFlipHorizontal
      

      Add this line:

      'Flip image
      If HFlip = True Then lookupPicture.Flip msoFlipHorizontal
      If VFlip = True Then lookupPicture.Flip msoFlipVertical
      

      The ZPosition is a whole number which determines the order of the images, where 1 is the bottom image.

  11. Ryleonardo says:

    Hello again Excel Off The Grid,
    Your help earlier has been invaluable.
    If I may ask for more, could you please advise on how the code can be modified so that I can make the pictures fit in 4 different ways:
    a) occupy the entire top1/3 of the cell (i.e. pic width=cell width, pic height=1/3 of cell height),
    b) occupy the middle 1/3 of the cell,
    c) occupy the bottom 1/3 of the cell,
    d) occupy the entire cell (i.e. exact fit),
    Thank you very much!

    • Excel Off The Grid says:

      Hi Ryleonardo,

      Add the following.

      Function:

      Public Function PictureLookup(Value As String, Location As Range, _
      Index As Integer, RotateBy As Integer, HFlip As Boolean, _
      VFlip As Boolean, resizePct As Double, ZPosition As Integer, _
      placementIndex As Integer)
      

      VBA Code to insert above PictureLookup = “”

      'Cell placement
      lookupPicture.LockAspectRatio = msoFalse
      lookupPicture.Width = Location.Width
      
      If placementIndex = 0 Then
          lookupPicture.Height = Location.Height
          lookupPicture.Top = picTop
      Else
          lookupPicture.Height = Location.Height / 3
          lookupPicture.Top = picTop + Location.Height / 3 * (placementIndex - 1)
      End If
      

      The placementIndex can have values of
      0 = Full Cell
      1 = Top
      2 = Middle
      3 = Bottom

  12. Guilherme says:

    Hello, thank you very much for the help!

    I’ve used the method 1, but in my sheet I use a lot of images which is making my sheet very slow.
    I’m thinking of trying the method 3 to see if it solves the speed problem. Is there a way of using pictures in the same workbook instead of pictures saved in other places like in the code below?

    Set lookupPicture = Sheets(sheetName).Shapes.AddPicture _
    (“C:\Users\marks\Documents\Flags\” & Value & “.png”, msoFalse, msoTrue, picLeft, picTop, -1, -1)

    • Excel Off The Grid says:

      Hi Guiherme,

      Method 1 would be quite slow, as linked images are resource hungry.

      Unfortunately, VBA can be quite slow too – you might just have to try it and see how you get on.

      In theory getting pictures from another worksheet should work. But might require a different approach. I will consider this for a future update to this post.

  13. Ryleonardo says:

    Thank you again Excel Off The Grid!
    Your new code works great!
    However, I don’t know why the code for rotation is now moving the image away from the target location. The larger the rotation number, the farther away.
    Could you please kindly advise?
    Thank you!

    • Excel Off The Grid says:

      Hi Ryleonardo,

      The order of the code is important, as it will change where the image finishes. Depending on your requirements the order may be different.

      My guess is that you want to resize, rotate, flip, then set the position.

  14. Amy says:

    Hi Mark,

    Thank you for the great information!
    I used the VBA option to insert multiple pictures into my excel sheet and it works great, however it has interrupted my ability to use the Ctrl+Z undo feature. Ctrl+Z now only works once instead of multiple times as usual.
    What can I do to fix this?

    Thank you!

  15. Excel Off The Grid says:

    Hi Amy,

    Each time the formula re-calculates it will clear the Undo stack. The Application.Volatile command at the start of the code forces the formula to recalculate each time a change is made.

    By removing that line of code it will still re-calculate, but only when one of the cells referenced by the formula changes, or their preceding cells change. That will help the Undo Stack to remain for longer.

  16. Stam says:

    Hey Excel Off The Grid,

    Excellent code. I ve been wondering though if there is a way to crop pictures before inserting them inside the worksheet. And also if there is a way to “grab” the pictures along with the cells including the name of the pictures and paste them in a word document.

    Thank you for your time.

    • Excel Off The Grid says:

      Hi Stam,

      In terms of cropping the picture, you could add something like the following.

      lookupPicture.PictureFormat.CropLeft = 50
      lookupPicture.PictureFormat.CropTop = 50
      lookupPicture.PictureFormat.CropRight = 50
      lookupPicture.PictureFormat.CropBottom = 50
      lookupPicture.Top = picTop
      lookupPicture.Left = picLeft
      

      If you want the crop settings to be included as part of the function, then you will need to create the variables within the function declaration.

      I don’t see why you couldn’t grab all the information you’ve mentioned and paste it into Word. I’ve got a post here, which might get you closer to what you want:
      https://exceloffthegrid.com/controlling-word-from-excel-using-vba/

  17. Donna Blizzard says:

    Hi I would like to link a Green 3D picture if a formula link number is positive and a Red 3D picture if the formula link is negative. How would I do this?

    • Excel Off The Grid says:

      Hi Donna,

      Thanks for your question. When you say a “3D picture”, what do you mean exactly?
      – What is the file format?
      – Do you mean a 3D model from the Insert Ribbon (Excel 365 & Excel 2019)
      – Is this already an object which exists on your worksheet?

  18. Mark Cottenham says:

    I help run a cycle club and help out with the results of daily racing. I would like to be able to automatically add a picture of either a gold, silver, bronze trophy into a cell next to the riders position. Is this possible?

    • Excel Off The Grid says:

      Hi Mark – yes this is possible, you would need to use the multiple images VBA method. But, this is probably not the best approach for your situation.

      I would recommend using a text characther for the icon and conditional formatting to crate the gold, silver or bronze color.

      Unfortuntately there isn’t a good trophy character in the standard character set or in wingdings/webdings, but maybe an alternative character would be suitable.

  19. Dianne L. says:

    Hi!
    You might just be my hero with Option 3 for multiple images. Is there a limit to how many images can be displayed? I’m working on a catalog to display prices and inventory for up to 600 items and they want photos of each! This gets updated weekly.

    We have this set up from a previous employee, but it was done by hand copying and pasting from one sheet (Database) to another (Compiled). I knew there had to be a better way and your option 3 appears to be it. But before I move forward I want to be sure it can handle so many images. Column A will hold the name and Column B the images.

    I’m a designer, not a programmer so any help would be immensely appreciated.

    • Excel Off The Grid says:

      Hi Dianne,

      I’ve not tried it with that many items. I suggest trying it with one image displayed 600 times and see how it goes. If the speed is reasonable and Excel can handle it, then try it with the 600 different images.

      Let me know how you get on. If it doesn’t work so well, there might be something else you could try.

  20. Anton Hudzik says:

    Hi, is there some option to embed the pictures in the cell? I need to filter the data, but when I filter, the pictures don’t get filtered, they rather stay in top…
    Thanks!

  21. Rahul Rawat says:

    Hi ,

    I am using by Index formula its show reference is not valid , actually i have to circulate it to team where they are in different locations so can not use VBA.

    My formula is =

    =INDEX(Sheet2!$B$3:$B$10,MATCH(Sheet2!$D$2,Sheet2!$A$3:$A$10,0))

    Please help me.

    • Excel Off The Grid says:

      Hi Rahul,
      The most likley reason is that the the value in Sheet2!$D$2 does not exist in Sheet2!$A$3:$A$10. Therefore the INDEX/MATCH will return an error, rather than a cell reference.

  22. James says:

    Excel Off The Grid,
    How can I get the VBA to display a default image if the specific image being requested does not exist within the file path? Thanks.

    • Excel Off The Grid says:

      Hi James,

      Why not use the IFERROR function with the default image used in the second argument.

      For example, it could be similar to the following. If B4 is not the name of a picture it will display the picture in B5, which is the default image.

      =IFERROR(PictureLookup(B4, E7, 1),PictureLookup(B5, E7, 1))
      • James says:

        Thanks! I was originally trying to incorporate an IFERROR bit within the VBA which kept breaking it. I didn’t think to just put the function within the worksheet. I ended up with =IFERROR(A1, PictureLookup(A4,B2,1)) which allowed the default image to be deleted on the next iteration. Much appreciated.

Leave a Reply

Your email address will not be published. Required fields are marked *