Here’s how you can have a photo change based on the selection from a dropdown list in Excel:
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
This post came from a question asked on a LinkedIn user group forum and the response referenced a dueling podcast between Mr. Excel and Excel Is Fun. Here is the link to the Dueling Podcast on this topic.
Many of the processes I will use here have been discussed in previous posts, so if you need a refresher on any of them, just search my site for that topic and you should find a related post.
The goal here is to create a dropdown list of photo names, and have the related photo appear.
Here are 4 photos and the names I want to associate with each:
Here is the result I want to achieve:
As with some previous blog posts, I would strongly encourage you to watch the video. I will go through the steps in a cursory fashion below, but the video will best explain the details to accomplish this.
Step 1: Load your photos onto a worksheet, and make sure each photo is contained within a single cell. In a future step we will be referencing the cell where that the photo is located, so this is an important step.
Step 2: Create the list for your Data Validation dropdown. I first named the list from cells A2:A5 as “Wine” using “Create from Selection” on the Formulas tab.
Step 3: Locate where you want the dropdown list, and using Data Validation – Lists, enter the list based on what you named it. In my case: “=Wine”:
Step 4: Name the cells where the photos are located. Again, I used “Create from Selection” and named cells B2:B5 based on the names in cells A2:A5.
Step 5: Click on the cell where the dropdown list is located, and create a named formula. To do this, use the keyboard shortcut Ctrl + F3, then click on “New”. I then wrapped the cell location in an INDIRECT function. INDIRECT converts a text string to an actual cell reference. I also named it “WinePhoto”. Now, any name we call up with the dropdown list will tie to the cell reference of that name that we did in Step 4:
Step 6: Finally, copy any of the photos to the desired location near the dropdown list, then click on the photo and in the Formula bar, enter = followed by the name of the formula from Step 5:
Now, any selection you make from the dropdown list should produce the photo of that name!