Showing a Picture Based on the Value of another Cell in Excel
In this walk-through ,I will show you how to use Excel to make a specific image show depending on a cells value, or by having a drop-down list
In this example, I want to show a list of products, and have an image of that product display when I select it.
This isn’t overly difficult, but does use some advanced techniques to make it work.
Add your pictures in Excel
First, you need to add your picture to Excel. You need to add them as a ‘list’, the name of the picture, and the picture itself in another column.
To add a picture, INSERT tab à Pictures, browse to your picture, and click INSERT. Resize and position the image as required
It’s very important that you ensure the pictures you add sits within the boundary of the cell – make the cell bigger as required.
Build the Lookup Formula
First, decide which cell will hold the value of the product you’re going to locate. This cell might have some validation based on the list you created so the user gets a list of values to select from, or if you’re familiar with using Form Controls, I linked a Drop Down form control to my list of values and did it that way. Whichever approach you take, you should end up with a cell containing the name of the product you’re trying to bring back.
In my example, I’m using cell C16
We now need to create a formula which will identify which cell the image is stored in. However, we’re going to create this as a Named Range, as we need to use it in a certain way later.
From the FORMULAS tab, open the Name Manager, and select ‘New’
Under ‘Name’, enter PRODUCT (or any unique name – no spaces). Under Refers To, you should modify and insert the following formula…
NOTE:
In hindsight, ‘PRODUCT’ isn’t the best name to use, as it’s also the name of a built-in function. Therefore, I would suggest PRODUCTIMG or something unique. Where ever you see PRODUCT, please read as PRODUCTIMG!
=INDIRECT(ADDRESS(Product_List_Row_Number-1+MATCH(Product_Selection, List_Of_Picture_Names, 0), Column_With_Pictures))
The Product_List_Row_Number should be the row number of your first product.
Product_Selection
is the cell containing the item you want to match (eg, C16 in this example)
List_Of_Picture_Names is your list of product names
Column_With_Pictures is the column number containing your images. If your images are in column C, you’d enter 3
My formula looks like this…
NOTE:
As pointed out by Dandy in the comments, if you plan to keep your images in a separate tab, you will need to update your INDIRECT formula to include a reference to the worksheet which contains your images (Product List in my example)
Therefore, your formula to use above would look like
=INDIRECT("'Product List'!"&ADDRESS(2-1+MATCH('Product List'!$R$8,'Product List'!$B$2:$B$4,0),3))
Click OK to close the New Name window, and Close on the Name Manager
Bring it all together
Now, copy any of your images – doesn’t matter which, and paste it where you want your dynamic image to appear
Now, select that image again. In the formula bar, which should currently to blank, enter ‘=PRODUCT’ (or whatever you named your formula in the previous step)
And that’s it – when you change your product, the image should change with it.
If your product changes, but isn’t quite bringing back the right image, tweak your names formula. Ensure the range you selected for the List_Of_Picture_Values begins on the same row as your Product_List_Row_Number, and ensure the column number is right
You can then build all your other information around this, as I have done.
When I go to the last step where we write =Product into our formula bar I get a “Reference is not valid.” error. Any idea why?
It’s because you are probably keeping your images in another Worksheet (and it is much more elegant this way).
Originally, the INDIRECT function references values on its own sheet. So, to avoid that, you must append the Worksheet name before the ADDRESS function.
It will look something like this:
=INDIRECT(“image_sheet!”&ADDRESS(….
— do not forget to end the sheet name with an exclamation symbol (!) —
Hi there, great little write-up on dynamic picture.
I have a question regarding the Production_Selection. On your example, you showed a drop-down list, so Product_Selection is always referring to the same cell. Is there a possible way to have it link to say cell A1 to A10?
Hi Thank you for this. But it seems when i get to the last step. I select the picture and try to type on the formula =product. I cannot do that? Seems the formula is blocked i cannot type anything. Am i missing a step?
Hi Sami, a couple of things to try…
1) In hindsight, ‘PRODUCT’ isn’t the best name to have suggested, as it’s also the name of a built-in function, and it could be conflicting.
Try renaming the range to PRODUCTIMG (Formulas–>Name Manager–>PRODUCT–>Edit–>[Change Name]–>OK)
Then use =PRODUCTIMG instead
2) If it still does not work, try, in any empty cell, =PRODUCT / =PRODUCTIMG
It should return a ‘0’ value.
If you get an error, then check you assigned the PRODUCT / PRODUCTIMG formula correctly
3) See the comments above the same step – could be that the same applies to you (I’ll have to update the tutorial to reflect this!)
I have the same trouble as Sami. I cannot access the formula bar when the image is selected. I am familiar with linking cell addresses to drawing shapes, so I tried linking “=Product” to a rectangle, and still I get nothing. I do get the ‘0’ value when typing it into a cell, and I get a ‘3’ if I type a 3 into the cell where the product image is, verifying the formula used with the name. No error message there. There is something about the image that is disallowing me from using the formula bar.
Same problem here. I’m using office 2007. I’ve used another name instead of PRODUCT or PRODUCTIMG like “ppp” and got a “0” when I assigned to a cell the value “=ppp”.
But wtill I can’t assign anything in the formula bar as I select any image (or clipart). what’s wrong? thank you
It is bad luck that this did not meet with more success. The simplest thing is to name a single cell containing a product, for example select C15 and type ‘bread’ in the Name box to create a single cell named range.
Selecting the output picture and you might see ‘Picture 1’ in the name box and, maybe, something in the formula bar. Overtype
=bread
in the formula bar and the picture should appear. Assuming that works, then the problem is with your formula which must contain an error..
As a more general observation, I would add that the simplest function that returns a reference to a range is INDEX. You could use MATCH to determine the product you require by index, then “productImage” to refer to the required image
= INDEX( Images, productIndex )
would save a lot of messing with address strings.
This works well, but what if we would like a whole row of drop down menus that would select the image. We would have to have a formula for each drop down menu. Is there an easier method to enhance to have this functionality?
hi, it is working. But while changing the name it the Image is not reflecting Properly. Please give your suggestion.
I am trying to select a photo at random with the following formula:
=IF(RAND()<0.5,B2,C2) where B2 and C2 have images locked to cell, sized correctly, etc. per all above suggestions and comments
When I try to enter the formula I get "This formula is missing a range reference or a defined name. "
However both of the following formulas work perfectly, each in turn selecting the correct embedded photo:
=B2 | =C2
I tried with the rand function in its own cell but have not been able to enter a formula for the image..
Any help appreciated. Thank You. ( Microsoft Home and Business 2016 with all Windows Updates, etc.)