Dynamic picture based on a reference cell.

[Dme615]

Is there way to have a dynamic picture work in conjunction with vlookup.

I have a sheet that is used to make labels and pulls from a “DATA” sheet. The data sheet has part numbers, suppliers, pictures of parts etc. I would like the picture to change based the reference cell. If I enter 159 in A4, cell f7 would display the picture for part 159 etc.
Due to a large quantity of parts a drop down menu would be a hindrance. And that’s the only way I have found to make it work.

 

[Yongle]

Is this how you want it to work?

 

[Dme615]

Is this how you want it to work?

Any idea if this would work with the data sheet being formatted as a table? The location would change if the table was sorted a different way. Part 159 may have been located in row 5 one time then row 148 the next depending on if someone is sorting based on supplier or alphabetical etc. All pictures would still be located in J column and would relocate with the sort but the “cell address” would change..

 

[Yongle]

I am away from my PC today
Perhaps you could test it yourself and let me know
- from memory ? I think the named range is likely to remain pointing at B2 after its contents have moved to B10 (due to sort)
- if that is so, a different method to achieve the lookup will be required

 

[Yongle]

Back at PC this morning
The following 2 behaviours were observed for a named cell resident within a structured Excel table

1. Insert a row above that cell
- result: named range is automatically re-referenced ✔
2. Sort the data
- result: named range stays with the original cell reference

My thoughts ...
- every formula solution I have seen (to lookup images) has used named ranges
- requirement permitting user to sort the data renders such solutions unworkable
- consider using VBA to meet your requirements

 

[Dme615]

I’m not familiar with VBA.
I have removed the table sort function and added links to the most common blocks of data.
I will look into learning VBA.
However After following the steps above in the video.The indirect formula in a linked picture/picture returns an invalid reference error. Any thoughts?
Thanks for your help!

 

[Yongle]

I will test the method in the video later today
Do your part numbers contain only numeric characters?

 

[Dme615]

No. Examples- 1BC, 29J, 204, 8Z6...

 

[pgc01]

Hi

Don't have much time at this moment, but my 2 cents...

This is a solution for the case of the pictures in fixed cells. Like you place the picture in J2 and it refers to the part in C2. you can change the value in C2 either manually or using sort and the picture updates.

If you define the names of the ranges where the pictures are in a standard way (for ex. prepending "pic_", like "pic_1BC", "pic_29J", etc) you can define the picture referencing the name in a defined cell.
For ex. if the part number is in column C you'd place the picture in column J referring to the name in the same row in column C.

You'd have to define a name for each picture, lots of work if you have many.

 

[Dme615]

I think I figured out the issue. But I’m not sure how to fix it.
When using the name manager excel wont allow the cell’s to be named in the exact format that my part numbers are in, B20, H28, 204. So it named them _B20,_H28 etc. And for the indirect function to work correctly it has to be an Exact match. Names have to start with a letter, underscore or backslash. And can’t contain hyphen/dashes. Or be mistaken for a cell address. That rules out using the short number B20 or the long number jgfz13-0004320 (Both of which populate on my Label sheet) .
So I need to find a way to name the cells that will allow someone to input part B20 without underscore etc and have the function work correctly.
Is there a way to have cell d4 copy a4 but add an underscore to the beginning of the text entered in a4?
A4 = B20
D4 shows _B20