How to use IF logic with linked pictures

[SimSum]

I am embedding images that change dynamically. The images are on a separate sheet, and are in a Named Range. On the display sheet, an image cell from the image sheet is copied over using Paste Special -> Linked Picture. Then I click on the new linked picture and replace the cell reference with the Named Range. So right now, if I click on the picture, the formula bar states

=MiniMap

This works perfectly after many many hours tweaking it.

If I select different criteria in some drop-downs, I get different maps, and again, this is all working splendidly. There are quite a few occurrences where an image will not be available, and for that I have photo shopped a No Image placeholder. I created its own Name as MissingMap. I'm trying to then do

=IF(TRUE,MiniMap,MissingMap)

where obviously TRUE is some criteria I am using. The criteria works in a standalone cell, and each named item works. When put together in the above IF, I get a

This formula is missing a range reference or a defined name

It is imperative that I be able to show the picture when it's available, or a placeholder if not. Any help appreciated to get that last feature working. Will definitely consider a Plan B on displaying one image or another based on a looked up criteria. TYIA!!

 

[mackc557]

Are you willing to give an example of your criteria that works by itself, but not in the IF statement?

 

[jkpieterse]

Put the IF formula in a named range and use that named range as the source of the picture.

 

[SimSum]

Are you willing to give an example of your criteria that works by itself, but not in the IF statement?

If verbally, then there is a cell in the table of images, next to each image, which is a 1 or 0. By default zero, then when I add an image, it's changed to 1. The IF logic is a VLookup for the zero or one and then use one image or the other. So if I apply IF(VLookup(blahblah)=1,"Yes","No") to a cell, I get Yes or No returned. When"Yes" or "No" are replaced with MiniMap and/or MissingMap, I get the error stated. I'm usually ok with figuring out a new approach based on an error, but the error given is either too vague or I just don't understand it. The error does not help me understand that this is not possible, or even how to Google it as it relates to what I'm trying to do. If you would like an example page uploaded, I can do that - just request it. And thank you for answer!

Put the IF formula in a named range and use that named range as the source of the picture.

Good sir, I am nowhere near expert enough at Excel to understand that, although I am trying to. I have read it a thousand times trying to see in my mind's eye what this is, and I've failed so far. But thank you for the reply. It is most appreciated!

 

[jkpieterse]

Click the Formula tab, create name. Name it and write that same if(vlookup(blahblah) into the refers to box. Ok your way back to excel. Now use the new name for the picture

 

[SimSum]

Click the Formula tab, create name. Name it and write that same if(vlookup(blahblah) into the refers to box. Ok your way back to excel. Now use the new name for the picture

MiniMap is a Named Range that is based on a table. Here is the lookup for that Name

=INDEX(iLibrary!$D$3:$F$18,MATCH(DATA!$D$3,iLibrary!$B$3:$B$18,0),1)

iLibrary is the image library worksheet that contains the image table
D3:F18 is the table matrix
DATA!D3 is the unique ID, on its own sheet, created by the drop-down boxes on the display sheet, and is a column in the image table
B3:B18 is the unique ID column in the table to match on, which then attaches a cell reference to the Name MiniMap

MissingMap is one cell reference, as it is only one image

When I do as you asked and create a new Name of TryThis and the formula

=IF(VLOOKUP(DATA!D3,Table1,2,FALSE)=1,MiniMap,MissingMap)

2 things occur. #1, when I replace the Image link with TryThis instead of MiniMap, I get a 'Reference isn't valid' error. And #2, when I go back into the name manager, the refers to box has changed my DATA!D3 entry into DATA! and a differing string of letters/numbers, and in fact, every time I switch a tab and re-check the manager, it is a new number there. Advice needed... and again, thank you for your patience.

 

[mackc557]

As with any normal cell, if you create a Name in the Name Manager you will need to use dollar signs ($) to lock the row/column. So the "Refers To:" box should have look like DATA!$D$3, otherwise DATA!D3 will change based on where the active cell is. This may not fix the entire problem, but that is why the numbers keep changing.

 

[SimSum]

Thank you Mack - that did fix the roaming reference! Thank you for teaching me about that for Named references. Will probably save me a post down the road!
But the 'Reference isn't valid' error unfortunately persists.

 

[jkpieterse]

Upload a sanitized copy of your file to somewhere and share a link here so we can have a look

 

[SimSum]

Upload a sanitized copy of your file to somewhere and share a link here so we can have a look

I will work on that Jan, thank you. I am revising the table quite a bit as this is a new and growing project. The ability to swap out images will remain, though, and I will need help to sort that out. Above all. the project is otherwise complete without any need for VBA - and I do hope to keep it that way. I will probably open a new thread with a much more generalized issue to solve, and whether there or back on this thread, I look forward to your advice. Thank you for your time, Jan!