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:
- Named range + INDEX/MATCH + linked picture (i.e. using a formula to insert a picture based on a cell value).
- Chart fill + #N/A (i.e. using a chart and formulas to insert a picture based on cell value).
- VBA User-Defined Function (i.e. using a macro to insert a picture based on a cell value).
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.
Next, we’ll create a named range by selecting Formulas -> Define Name.
In the New Name window create a new named range called “CountryLookup”.
This named range will refer to an INDEX/MATCH function containing the country name and the flags.
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).
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).
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.
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
If you recieve the “Reference isn’t valid” error there are two likley problems:
- 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”.
- 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.
The formula in Cell B2 is:
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.
A chart will appear. Right-click on the series, then click Select Data…
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.
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.
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:
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.
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:
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)
- Referencing a cell which contains a unique number (C2 contains the unique counter)
- Using the ROW() function (where the function is not repeated across the columns the ROW() number will be unique)
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