Dynamic Image based on Dropdown List [SOLVED]
1) In the first worksheet, enter column headings for the information you want to display about your pictures. The last column will be used to display the picture. Format the row below the headings for displaying the information related to picture.
2) Size the column width and row height of the last cell to be the desired size of your picture;
3) Next, copy and paste one of your pictures into the Image cell (cell D5 in this example). The picture should be close to the same size of the cell. If necessary, resize the picture or adjust the column width and row height so that the picture fits within the borders of the cell;
4) Next, copy the information (i.e. cells A4:D5) from the first sheet (Main) and paste it starting at cell A1 on a second sheet (Listing) where you will be storing your images and related information. Enter the names you want to be listed in your dropdown down the first column and enter the related information for each item in the other columns. Adjust the column widths to be the same as in the first worksheet and adjust all the rows (that will be containing pictures) below the headings to be the same height as the second row in the first sheet (i.e. the height of the images you will be using).
5) Copy the image from Step 2 and paste it into your image editing program (e.g Irfanview). You should now be able to see the size (in pixels) you will need for your other images (e.g. 120 x 120 pixels).
6) For each picture you will be using, copy it into your image editing program. Edit and resize it using the dimensions you established in the previous step. Then paste it into the appropriate cell in the Image column on the Listing sheet. All picture must be the same size and fit within the cell;
Once you have all of your images and related information entered on your Listing sheet, you are now ready to create a dropdown on the first sheet (Main) and the formulas that will return the image and related information.
7) For the purpose of this example, assuming you have 10 items for your dropdown values plus two additional columns of related information and a column for images, the range on your Listing sheet that you will be working with is A1:D11 (Row 1 contains column headings).
8) To add the dropdown, select cell A5 (on the Main sheet) then, from the Data tab, click Data Validation, and List. Click in the Source box, then select the 10 items in the first column of your Listing sheet (i.e. cells A2:A11) and click OK. You will see a small dropdown arrow appear next to the cell. Click on it and you will see a listing of all items in your list. Select an item from the list and it will be displayed in the cell.
9) You will use a combination of INDEX and MATCH functions to return information from the Listing sheet based on the item selected from the dropdown,
On the Listing sheet, enter these formulas…
Cell B5 =INDEX(Listing!$A$2:$D$11,MATCH($A$5,Listing!$A$2:$A$11,0),2)
Cell C5 =INDEX(Listing!$A$2:$D$11,MATCH($A$5,Listing!$A$2:$A$11,0),3)
Cell D5 =INDEX(Listing!$A$2:$D$11,MATCH($A$5,Listing!$A$2:$A$11,0),4)
The formulas in the 2nd and 3rd column return the related information from the Listing sheet. But notice that the formula in the 4th column (Image) shows zero. (Temporarily move the image out of the way). But that’s OK because next I want you to create a name in the Name Manager using the formula from this cell. That is where the magic will happen.
10) Select the cell (D5) in the Image column. You will see the formula in the Formula Bar;
11) Click in the Formula Bar and drag to highlight the formula text. Press CTRL+C to copy it to the Clipboard. Then press the Escape key to exit Formula Edit Mode.
12) From the Formulas tab, select Name Manager and click New…. In the Name field type myImage (you can use a different name if you want). Click in the Refers to box, highlight and delete the existing formula, then press CTRL+V to paste the formula you copied from the Image column cell. (If you want, you can drag the right edge of the New Name dialog so you can see the entire formula). Click OK.
13) Now, this is where my tip from two years ago becomes very useful. Select the picture and, in the Formula Bar, type =myImage (don’t forget to type the equals sign) and press Enter.
14) Click in cell A5 and make a different selection from the dropdown and the picture in column D should change.
What makes this work is the fact that instead of linking a picture to a specific cell that shows an image of the cell (as in my original tip), instead we linked the picture to a Named Formula.
And since the name myImage refers to the formula…
…and the result of this formula is dependent on the value in cell A5 (i.e. the value selected from the dropdown), the result returned to cell D5 is a link, by way of the named picture, to the cell in column 4 of the Listing sheet that this formula refers to.
Caution: Keep in mind that adding a lot of pictures to your workbook can significantly increase the file size. You may need to experiment with this to determine what effect the number pictures and their size will have for you.