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.’

 

[CoogansBluff]

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

Thanks again for reply. I don't think the the "refert-to" formula is the problem because I've got formulas that work for some but can't link them up to other dummy images. As for what I'm copying, to get the dummy image, I'm copying anything in hopes that something works. I'm putting an image entirely in a cell, copying the cell (not the image), and pasting w/ link. Then I try to rename that pasted image (renaming the image, not the cell) =AwayBatter1.

I can send you the spreadsheet if you like, although it's pretty big, so I might have to shave it down.

 

[CoogansBluff]

Now it seems to be working again. I don't what I'm doing different to make it work for a spell, then stop working.

 

[Anthony47]

Why would you assign to an image the name "=AwayBetter"? The string "=AwayBetter", I mean. While the character "=" is accepted as a name, there are names less confusing, for example "IMG1", "Dummy1" and so on.

Again, if (a) the formula does not respect that specific syntax, or (b) if at the moment that you try assigning the name to a dummy image (in the formula bar, using the notation =TheNamedInterval) the formula returns an error then you will not be allowed to complete the assignment. And in most of the cases (b) depends on (a).
The same name already assigned to another dummy image will not raise any error.

if you wish sharing a failing workbook (filled with the images to be picked, the named ranges, the dummy images and their formulas), upload it to a filesharing server and publish the download link.

Bye

 

[CoogansBluff]

Why would you assign to an image the name "=AwayBetter"? The string "=AwayBetter", I mean. While the character "=" is accepted as a name, there are names less confusing, for example "IMG1", "Dummy1" and so on.

Again, if (a) the formula does not respect that specific syntax, or (b) if at the moment that you try assigning the name to a dummy image (in the formula bar, using the notation =TheNamedInterval) the formula returns an error then you will not be allowed to complete the assignment. And in most of the cases (b) depends on (a).
The same name already assigned to another dummy image will not raise any error.

if you wish sharing a failing workbook (filled with the images to be picked, the named ranges, the dummy images and their formulas), upload it to a filesharing server and publish the download link.

Bye

Anthony - Thanks again. Let me process all this when I have a bit of time. Will followup.

 

[CoogansBluff]

Why would you assign to an image the name "=AwayBetter"? The string "=AwayBetter", I mean. While the character "=" is accepted as a name, there are names less confusing, for example "IMG1", "Dummy1" and so on.

=AwayBatter1 is what I put in the formula bar. What I said earlier was confusing or wrong. I didn't change the name of the dummy image. Just left it as Picture 6 or whatever it's originally called. And the formulas that I've constructed work sometimes and not others, so I don't think it's a formula syntax error. Just something in the process of copying/creating dummy photos and assigning that must be going afoul. I've actually completed my original project, feeling lucky that I got enough to work. But it's such a cool feature that I'm sure I'll want to do it again at some point, but finding that it works sometimes, other times not, and I can't decipher what I'm doing differently each time to cause such unpredictability.

 

[Anthony47]

the formulas that I've constructed work sometimes and not others, so I don't think it's a formula syntax error.

Believe me: if you don't respect the syntax when defining the names, sometime it works and some others it will not
As well, if a Name is in error it will be ok with the dummy images that you already assigned it to, but will rise that error if you try assigning it to a new dummy image

As a demo, refer to the file you can download from here: DEMO_INSET.xlsx

Everithing is located in sheet "Foglio1"
The main image on the right is 50 columns * 50 rows; its a lunar map.
The dummy image #1 is located in A4:D14

A named range has been inserted; its name is INSET, its formula is =OFFSET(Foglio1!$L$1,MATCH(Foglio1!$I$2,Foglio1!$L$1:$L$51,0)-1,MATCH(Foglio1!$H$2,Foglio1!$L$1:$BJ$1,0)-1,4,6)
(this is overcomplicated because I wish to use MATCH to calculate the orizontal and vertical offsets)

The formula =INSET has been assigned to dummy image #1

Two scroll bars let you control the horizontal and vertical offset, that is the area pointed by INSET and thus wich portion of the image will be shown into the dummy image

Scroll bars values ranges from 0 to 100 (whereas a MATCH occours only in the range 0-50, for both horizontal and vertical offset)

Now if you move the scroll bars you will see that a different portion of the big image is shown into dummy image #1
The result of INSET is also shown in cell D16 (in most of the cases it will be 0, unless either the horiz or the vert offset be 0; or it will be an error)

If you move one of the scroll bar beyond 50 then in D16 you will have the error #N/A, and the dummy image will show the previous portion of the moon

On the worksheet there is a second image, in A18 and below.
If you try to assign this image the same formula as dummy #1 (ie =INSET) while D16 is in error you will get an error message and the operation cannot be completed; move back the scroll bars to bring both the offset to 50 or below and you will be able to assign the formula also to the second dummy image.

If you experience an unstable situation with your solution, then share a sample workbook and we shall try investigating the situation.

Bye