Linked images and 'Reference is not valid'

CoogansBluff

New Member
Joined
Mar 7, 2021
Messages
15
Office Version
  1. 2013
Platform
  1. Windows
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.’
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
Would it be possible that you mistyped "MATCH(Master!$BS$10" instead of MATCH(Master!$B$10 ?
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
What a pity :biggrin:

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
Excel Formula:
=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

New Member
Joined
Mar 7, 2021
Messages
15
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

What a pity :biggrin:

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
Excel Formula:
=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

New Member
Joined
Mar 7, 2021
Messages
15
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Mar 29, 2006
Messages
2,147

ADVERTISEMENT

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

New Member
Joined
Mar 7, 2021
Messages
15
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Mar 7, 2021
Messages
15
Office Version
  1. 2013
Platform
  1. Windows
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.

Reference not valid.png
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,591
Messages
5,637,288
Members
416,963
Latest member
samfuge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top