The Two Most Common Errors When Selecting Photos From A Drop Down List In Excel

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:

For Excel 2010 & 2013

For Excel 2007

An UPDATE to address spaces and some other twists

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:

12716-1

If I use column A for my Data Validation drop down list I get:

12716-2

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:

12716-3

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:

12716-4

However, if there is a selection made when I paste the photo and enter the formula name in the formula bar, it works properly:

12716-5

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!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , , ,

4 Responses to The Two Most Common Errors When Selecting Photos From A Drop Down List In Excel

  1. Diana October 14, 2016 at 5:01 pm #

    Hello,
    I think my problem is more so that I can’t make the indirect formula to work. I click on the selection box (no blank option) then ctrl F3 I write INDIRECT() with the selected cell inbetween the parenthesis and it doesn’t show that the image is selected and afterwards like it is supposed to? Therefore when i try to go to the next step it says reference invalid.
    Thank you for your help!

  2. Andy October 28, 2016 at 11:13 am #

    Hi

    I think im having the same problem as Diana. I followed your video with a test sheet and it worked fine. Now im trying to do it on a project and it keeps coming up with reference not valid.

    Am I ok to send across the file in an email?

Leave a Reply