Picture Lookup

[Kutty]

Hi!
I retrieve picture, using Index & Match functions. I have picture data in one sheet & contact details in another sheet. If we retrieve the picture in one row it may possible. But I want picture so many rows. It may be want so many named range. Is there any other way to do solve this problem?

 

[Chihiro]

Upload sample workbook. Without it, we'll be guessing as to how you set up the workbook.

 

[Peter Bartholomew]

You can link a picture to a dynamic range by using a name such as 'selectedPicture' that refers to

=INDEX(pictures,n,1):INDEX(pictures,n+4,2)

I suspect OFFSET would work just as well.

 

[Kutty]

Hi @Chihiro
Sorry for that inconvenient. Here it is the sample file.

 

[Kutty]

You can link a picture to a dynamic range by using a name such as 'selectedPicture' that refers to

=INDEX(pictures,n,1):INDEX(pictures,n+4,2)

I suspect OFFSET would work just as well.

Hi Peter
I'm not understand your formula

 

[Chihiro]

Hmm, can't think of way to do it without small VBA code off top of my head.
Are you open to it?

 

[Peter Bartholomew]

Sorry, I thought you were saying that the pictures you were reproducing covered multiple cells. If you do not wish to use VBA you are stuck with one name per picture since pictures are not part of the worksheet grid.

From code, if you use the 'on action' property of the shape, you can read its name using Application.Caller, you can then read the position of the top left corner of the picture or you could parse the shape name to determine which person is being referenced. You could then select the shape and set its formula property to reference the target picture.

At that point, I would recommend calculating the worksheet and finally delete the formula. Dynamic formulas for pictures appear to be very resource intensive. The experience in the link below might be of relevance to you.

https://www.spreadsheet1.com/how-our-world-cup-2018-spreadsheet-is-made.html

 

[bosco_yip]

Another option to use INDIRECT function instead of INDEX+MATCH function.

1] In Sheet2, select cells A2:B8 >> Defined Name >> Create from selection >> Tick the box of "Left column" >> OK

2] Define name and refers to formula :

PicL1: =INDIRECT(Sheet1!$B$2)
PicL2: =INDIRECT(Sheet1!$B$3)
PicL3: =INDIRECT(Sheet1!$B$4)
PicL4: =INDIRECT(Sheet1!$B$5)
PicL5: =INDIRECT(Sheet1!$B$6)

3] In Sheet2, C2:C6 image , click the picture >> insert: =PicL1 and insert: =PicL2……. one by one.

4] see attachment

Regards
Bosco

 

[Kutty]

Sorry, I thought you were saying that the pictures you were reproducing covered multiple cells. If you do not wish to use VBA you are stuck with one name per picture since pictures are not part of the worksheet grid.

From code, if you use the 'on action' property of the shape, you can read its name using Application.Caller, you can then read the position of the top left corner of the picture or you could parse the shape name to determine which person is being referenced. You could then select the shape and set its formula property to reference the target picture.

At that point, I would recommend calculating the worksheet and finally delete the formula. Dynamic formulas for pictures appear to be very resource intensive. The experience in the link below might be of relevance to you.

https://www.spreadsheet1.com/how-our-world-cup-2018-spreadsheet-is-made.html

Hi @Peter Bartholomew
This one is what i want.
I think without VBA its impossible..
Thanks for sharing this spreadsheet.

 

[Kutty]

Hmm, can't think of way to do it without small VBA code off top of my head.
Are you open to it?

Hi Chihiro
I accept it.
Thanks.

 

[Kutty]

Another option to use INDIRECT function instead of INDEX+MATCH function.

1] In Sheet2, select cells A2:B8 >> Defined Name >> Create from selection >> Tick the box of "Left column" >> OK

2] Define name and refers to formula :

PicL1: =INDIRECT(Sheet1!$B$2)
PicL2: =INDIRECT(Sheet1!$B$3)
PicL3: =INDIRECT(Sheet1!$B$4)
PicL4: =INDIRECT(Sheet1!$B$5)
PicL5: =INDIRECT(Sheet1!$B$6)

3] In Sheet2, C2:C6 image , click the picture >> insert: =PicL1 and insert: =PicL2……. one by one.

4] see attachment

Regards
Bosco

Hi bosco_yip
Thanks for your workout. It has so many named range. I have try to avoid many named formula. If above 500 bio-data and Photo, it may not possible. That is why I am asking any other method to eliminate so many named formula. Thanks

 

[Chihiro]

Here's sample workbook with vba set up.

 

[Kutty]

Here's sample workbook with vba set up.

Hi Chihiro
Thank You so much. Your work sheet is so helpful to me. I don't know VBA. But here after I would like to learn. Thanks a lot.