0

I use Windows 7 Professional and Excel 2013.

I would like to change dynamically a picture on a worksheet based on a value from a dropdown list. I found online 2 ways that seems to fit my problem. However, I can't seem to make either of them work.

My dropdown list is located on my first worksheet named "Product Dashboard" in cell D20. It refers to a list of bottle names in another worksheet called "Bottle" in cell: "A2:A50" In the same worksheet "Bottle" in cell: "K2:K50" I have a serie of bottle picture corresponding to the bottle names of A2:A50. I would like to make the bottle picture corresponding to the value in the drop down list appear dynamically approximatley around the L20:N26 area. See pictures.Product dashboard worksheetBottle worksheet

the 2 methods that I found online and can basically understand involve named range: https://exceloffthegrid.com/automatically-change-picture/ https://www.extendoffice.com/documents/excel/4025-excel-dynamic-image-in-cell.html

in Formula/new name, I tried to define a new range "Bottleimagelookup" referring to either one of these 2 formulas:

=INDEX(Table1[Picture],MATCH('Product Dashboard'!$D$20,+Table1[Bottle Name],0))

or

=INDIRECT(ADDRESS(2-1+MATCH('Product Dashboard'!$D$20,+Table1[Bottle Name], 0), 11))

However, each time, excel tells me: "We found a problem with this formula. Try clicking Insert Function ,etc.". See picture.enter image description here

Do you know what is it that I am doing wrong? Would you know how to correct the formulas above or have a way that could solve my initial problem?

Thanks in advance for you help!

  • Why do you have the + before Table1in your MATCH() formulas? I don’t see it in either of the reference pages you cited, and it looks superfluous at best, and possibly irregular.  Try it without the +. – Scott Dec 26 '17 at 3:10
  • Thank you for your answer! I did what you recommanded (I also replaced the "," in the formula by ";") and now the range is nmaed without problem. – user856169 Dec 27 '17 at 8:21
1

Yes, I tested please change your formula to:

=INDEX(Table1[Picture],MATCH('Product Dashboard'!$D$20,Table1[Bottle Name],0))

or

=INDIRECT(ADDRESS(2-1+MATCH('Product Dashboard'!$D$20,Table1[Bottle Name], 0), 11))

Remove the + from your formulas.

enter image description here

enter image description here

|improve this answer|||||
  • Thank you for your answer! I did what you recommanded (I also replaced the "," in the formula by ";") and now the range is named without problem! So now I have a new range named "Bottleimagelookup". However, a new problem appears. After naming the range, I have to copy paste one of the picture where I want the relevant picture to appear. Then select the pasted picture and change the adress in the formula bar by the new named range ( in my case "Bottleimagelookup"). However, when I do so, Excel now tells me that the "Reference is not valid." Do you know know what I did wrong? – user856169 Dec 27 '17 at 9:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.