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!
Best Answer
Yes, I tested please change your formula to:
or
Remove the
+
from your formulas.