Automatically Display Image from Sheet based on Value in Adjacent Cell
How to display an image from a worksheet in the cell when I type a number or code in an Excel neighboring cells.
In this tutorial I'll show how you can display images or photos in a cell, which is based on the value in the adjacent cell. In the picture below you see the situation and the table into which to display images from a worksheet based on the values or codes in the adjacent cell (C2, C4, C6) located above the cell where we need to display the image. For this tutorial, all actions are in an active worksheet "Sheet3".
In the first step you need to select the cell 'F9' then click on the 'Copy button' on the 'Home tab' of the ribbon.
After clicking the Copy select the cell 'C3' and then click on the arrow button to open the Paste drop-down menu and then click on the 'Linked Picture (I)'. This option you have in Excel 2007-2013.
Instead these steps after selecting a cell 'F9' you can simply click on the Camera Tool (Please find Camera Tool tutorial using search box on this web site) and paste it into a cell (Excel will create a link). If you do not have the Camera icon on the Quick Access Toolbar, add it from the menu 'Command Not in the Ribbon'.
In Excel 2003, this option does not exist, so if you use Excel 2003, proceed as follows.
- Click on the cell 'F9' (do not select an image, but only the cell in which the image)
- Pushing the Shift key and hold it, click on the Edit menu.
- Click Copy
- Select the cell 'C3' where you want to paste the image.
- Holding down the SHIFT key, click the Edit menu => Paste Picture Link
Now you have a situation that is in cell 'C3' is Shape with a picture from a cell 'F9'. Size of Shape (form) is the same size as the cells 'F9' where we copied the picture. So it is necessary to adjust the size Shape copied image. If at the very beginning set the size of the cell F9:F15 identical size of connected cells B3, C3, B5, C5, B7:C7 then you will not have to adjust the dimensions and size of the copied Shape.
It looks like as shown below. You notice that Shape (frame) of the copied image is not the same size as merged cells 'B3' 'C3', and this is because cells 'F9' smaller. Therefore, this action displays an image of the same size, which is located in cell 'F9'.
How to create a new name for Image/Picture, in Excel Name Manager
In the next step should be the Formulas tab, click on the 'Name Manager' command. In the Name Manager, you need to create three items with formulas,
Let's create a formula for cell 'C2' and based on that will show an image below using the formula we need to set some conditions. Click the New button, and then in the 'Name' type 'Picture1' in the 'Refers to' enter the following formula
This creation of New Name, repeat two more times because we need the results of the cell in which the code (click on the OK button and repeat this action for the following cell C4 and C6.
Cells 'C4': picture2
Cell 'C6': Picture3
The end result of creating new items in the Name Manager as shown below.
In the next step you need to select Shape link the copied image, and then in 'Formula Bar' change the formula '=$F$9' in to '=picture1' (see picture bellow).
Automatically add images into the cell when I type a the code, number or text
Given the situation with the first image we should also display the picture in cells B5 and B7. So for cell B5 (Picture2) and B7 (Picture3) need to repeat steps 1 and 2 as well as step 4.
Finally, our results are as follows. Enter the code in cell C2, C4 and C6 in the cells below appear in the image based on the value or string. Also note that instead of numbers, the values you set as a condition of the text (eg. Country name or product).
Aslo, You can see other related tutorials: