Hi,

I'm trying to set up a program for work in excel that has a drop down list, and associated pictures that appear in another cell when the different options on the list are selected. I got this to work once in a practice worksheet using the following method:



Select Sheet2 and turn off Grid Lines
...<Tools>Options><View tab>Uncheck: Grid Lines

1)For each picture to be displayed:
1a. <Insert><Picture from file> (select picture and put it in the sheet).
1b. Select the range of cells that contains the picture.
1c. Name that range of cells, using the prefix "pic" followed by the dropdown list text:
Example for a picture of an Elephant:
<Insert><Name><Define>
Names in Workbook: picElephant

2)Build your data validation list on a cell in Sheet1 and pick one of the items.

3)Create a dynamic range name that refers to that cell:
<Insert><Name><Define>
Names in Workbook: ShowMyPic
RefersTo: =INDIRECT("pic"&Sheet1!$A$1)
...or whatever cell you chose.

4)Copy/Paste one of the pictures from Sheet2 to the display cell on Sheet1.

5)With the picture selected, type this in the formula bar, then press [Enter]:
=ShowMyPic

The picture will be replaced by the picture referred to by the dropdown list.

Each time you select a different item in the list, the associated picture
will appear in the picture box and resize appropriately.

However, when I went to apply the same method in my actual program/worksheet it isn't working. The issue is that when I do steps 4 and 5, instead of the correct picture coming in and changing as I select different items on the list, I simply get a reading in the first box where the picture was inserted reading #VALUE!, or if I put the dropdown on the very first pic it will display 0 in the box where the pic should be showing.

I know that my pictures are correctly linked to the drop down list because if I use Edit>Go To, it will take me to the correctly associated picture to the name in the list. But I am at a complete loss from there as to why ShowMyPic isn't working to bring in the pictures.

Any help would be greatly appreciated! I've been trying to fix it for hours and just can't seem to get anywhere...