Linked images and 'Reference is not valid'

[CoogansBluff]

I’m attempting to have images show up when a certain value appears in a certain cell.

So, on one sheet, I have a column of head shots of people, and in an adjacent column I have a list of their names. (If it’s a photo of Jimmy Stewart, then the name Jimmy Stewart is in the cell beside it.)

I then take a random photo, copy it, and use Name Manager, defining it this way:

=INDEX(Pictures!$C$3:$C$50,MATCH(Master!$BS$10,Pictures!$B$3:$B$50,0))

This linked photo then should change depending on the value in a particular cell. So it changes from an image of Jimmy Stewart to John Wayne to Katherine Hepburn depending on another cell.

I’ve watched multiple online videos, and this process works great – but I can never replicate it.

One of the videos had an example that could be downloaded, and I downloaded it, moved it into my workbook, and it worked fine. I actually converted what he had (photos of flags w/ countries) to what I’m doing (photos with names), and it works perfectly.

However, I need to have 20 linked photos working instead of just one so that I have 20 photos changing based on 20 cell values.

Whenever I try to make the linked photo work, I get ‘Reference is not valid.’

 

[Anthony47]

Would it be possible that you mistyped "MATCH(Master!$BS$10" instead of MATCH(Master!$B$10 ?

 

[CoogansBluff]

Would it be possible that you mistyped "MATCH(Master!$BS$10" instead of MATCH(Master!$B$10 ?

Logical question, but no, it's actually a cell on the far right of the sheet.

 

[Anthony47]

What a pity

Are you sure that what you enter in Master!$BS$10 has a match in Pictures!$B$3:$B$50?
Try modifying the Name "refer-to" formula to

=INDEX(Pictures!$C$3:$C$50,Master!$BS$9)

Set in Master!$BS$9 a valid index (1 to 50) then try reassigning the name to the "dummy" picture
If the error disappear then play with the value in Master!$BS$9 and check how it behave (Master!$BS$9 emulate the MATCH)
It it works in this way then the problem is with MATCH(Master!$BS$10,Pictures!$B$3:$B$50,0) that probably returns an error.

Bye

 

[CoogansBluff]

What a pity

Are you sure that what you enter in Master!$BS$10 has a match in Pictures!$B$3:$B$50?
Try modifying the Name "refer-to" formula to

=INDEX(Pictures!$C$3:$C$50,Master!$BS$9)

Set in Master!$BS$9 a valid index (1 to 50) then try reassigning the name to the "dummy" picture
If the error disappear then play with the value in Master!$BS$9 and check how it behave (Master!$BS$9 emulate the MATCH)
It it works in this way then the problem is with MATCH(Master!$BS$10,Pictures!$B$3:$B$50,0) that probably returns an error.

Bye

Yes, it does have a match in Pictures!$B$3:$B$50. And in fact, it's working to produce the image. The problem is that I'm trying to produce 19 more just like it, and I can't replicate it. I got the original to work by downloading someone else's example. If that makes sense. If I had just one place that I wanted these images to appear, I'd be golden. But I've got 20.

 

[CoogansBluff]

After more tinkering, I've gotten a second image to work right, so maybe I'm on to something. Could be that I was copying an image instead of the cell that contains the image. ... Will update if that proves to be the case.

 

[Anthony47]

Lets suppose that your range has been named "ALPHA"
Insert a new "dummy" picture (any picture) on sheet Master; now select the picture, go to the formula bar and type =ALPHA
This should change the dummy image, that now will show what is the content of ALPHA; since I guess you wish to show different images in your "dummy" pictures the you have to define more "names" and assign them to the several dummy pictures

This is the theory

 

[CoogansBluff]

Lets suppose that your range has been named "ALPHA"
Insert a new "dummy" picture (any picture) on sheet Master; now select the picture, go to the formula bar and type =ALPHA
This should change the dummy image, that now will show what is the content of ALPHA; since I guess you wish to show different images in your "dummy" pictures the you have to define more "names" and assign them to the several dummy pictures

This is the theory

Thanks, that's helpful. Looks like I'll need to have 20 defined names and 20 dummy pictures. Which is fine as long as I can get 'em all to work. I have a little more hope this a.m. than all of yesterday.

 

[CoogansBluff]

Well, I got it to work for several images, completing half my project, but suddenly I'm getting 'Reference is not valid' again when trying to name copied images.''

I'm copying the cell to the left, pasting a linked copy to the right, then naming that linked copy image =HomeBatter1. Reference not valid. It's not the formula. It's something in the process of copying and naming, IMO.

 

[Anthony47]

When you assign the Name to a dummy image the Name must return a valid reference; so make sure that you compile the field used by the MATCH function (Master!BS10, in your first Name) before assigning the name.
Also, make sure that in the "refert-to" formula you always use absolute addressing and Worksheet name, even if it looks un-necessary; for example:
Correct: INDEX(Pictures!$C$3:$C$50,MATCH(Master!$BS$10,Pictures!$B$3:$B$50,0))
Wrong: INDEX(Pictures!$C$3:$C$50,MATCH($BS$10,Pictures!$B$3:$B$50,0))
Wrong: INDEX(Pictures!$C$3:$C$50,MATCH(Master!BS10,Pictures!$B$3:$B$50,0))

Finally: What are you copying?? The process is: generate a correct Name and make sure has a valid returns; insert a dummy image; assign this dummy image a Name

Bye