Previously I’ve done tutorials on choosing photos for catalogs or proposals using a Data Validation drop down list for Excel 2013 (also works for 2010). However, using Excel 2007 requires a slightly different process. So here is how to do it in Excel 2007.
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.
Here are the links to previous tutorials for Excel 2010 & 2013:
Here are 4 photos and the names I want to associate with each:
Here is the result I want to achieve:
As with 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 “WinePic”. 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: Now, click in the cell on the Catalog tab where you want the image to appear. Then go to the Insert tab and select Object to the far right in the Text group and click on Bitmap Image:
When you click OK, the “Paint” application might open up. Close that and you will see a large blank box.
Step 7: Finally, while that box is selected, go up to the formula bar and replace “=EMBED(“Paint.Picture”,””)” with “=WinePic” and hit Enter:
Now, any selection you make from the dropdown list should produce the photo of that name!
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!