I’ve done three posts in the past on how to select photos using a drop down list. This series has turned out to be my most popular of all my posts. With all the questions and comments I get, there are two issues that are the most common errors that folks commit when setting up this process. This blog post will address those two issues.
My three previous posts on this topic are:
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.
The two biggest errors that folks make when creating this process are:
1) Names of the cells containing photos don’t match the entries in the drop down list
2) Invalid reference error
Issue #1 – Names don’t match
The primary culprit of this issue is spaces. Often I see that there is a list of names or categories that have spaces in them and that list is used in the Data Validation drop down list. That’s fine, except if you use that same list to create the names of the cells containing the photos by utilizing the Create From Selection option, Excel does not allow spaces for named cells or ranges. By default Excel will insert an underscore “_” where the spaces are.
Here are the photos and names associated with them:
If I use column A for my Data Validation drop down list I get:
However, when I use Create from Selection, or any other method to name the cells containing the photos, Excel will insert an underscore in lieu of the spaces:
Notice cell B2 is named “grape_bunch” instead of “grape bunch”. Since these don’t exactly match, Excel will never be able to pull the proper photo. Two alternatives are:
1) Insert an underscore in the list that creates the Data Validation drop down list
2) Use an alternative process as I describe in my UPDATE post – the link is listed at the top of this post. In this process we use VLOOKUP to pull the name that includes the underscore when we select one that has a space.
Issue #2 – Invalid Reference Error
The main issue here is not having a selection made from your drop down list when you copy the photo to its final location and associate it with the name for your INDIRECT formula.
Here is an example of what happens when you don’t have a selection made:
However, if there is a selection made when I paste the photo and enter the formula name in the formula bar, it works properly:
I hope this tutorial will help you with fixes to some common errors when you are creating your document using the unique process.
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!