Is there a way to Pass a image between sheets using =INDEX

[RudeBoy]

I currently am using this Code

=INDEX('NHL & NBA Logo''s'!A65:A93,MATCH(F4,'NHL & NBA Logo''s'!A65:A93,0))

to match a key word in sheet2 with another table in sheet1('NHL & NBA Logo''s').. Now once I get a match I would like to pass a Picture over to sheet2 from sheet1.. Now in Sheet1 I have the image and when I click on it with the mouse it says Picture 306.. All my images in that sheet have a different assigned number for each picture..

Thanks.. I hope someone can help..

 

[RudeBoy]

How can I Pass an image from one sheet to another

Okay I still have not got this one doen from 2004.. ha ha Okay here is the formula I am using :
=INDEX('NHL & NBA Logo''s'!A65:A93,MATCH(F4,'NHL & NBA Logo''s'!A65:A93,0))

Now in the other sheet there are images and underneath the image in the cell I have a name defined..

Now the actual image is on A79 but when I click on the image in that folder it says Picture 308.. So maybe in my formula I need to have it pushing Picture 308 ?? Helppppppp

thanks for all the help..

 

[daniels012]

What if you put the image on page 2 and write code to hide it or unhide it under certain conditions?

Michael

 

[RudeBoy]

Well actually the way I have it set-up is.. On sheet one I have all the NHL logos for every team.. On Sheet two I have my Play-offs skeleton.. Each season I plug in the name of the team that makes it to the playoffs.. So in 2004 The detroit red wings made it.. So I have the name Red Wings in F4 on that one sheet so wha it does is go do a match on sheet one for the undelying name I have and then it gives me the Detroit Red Wings.. But what I really want is the LOG from the Red Wings..

 

[Joe Was]

You missed the point.

My post indicated "[This is important!]"

You must name every picture to work with it in a formula!

The index changes so it only does you any good if you are working with the elements of the current collection as in a loop. So, you must name every object as it is created to be able to refer to it at a latter time.

Then your formula must use a custom UserDefined Function or it will fail!

Your solution is best served with VBA code, Sheet Functions can only go so far and they are messy or limited when applyed to objects.

 

[RudeBoy]

Joe there you are.. I had sent you a side message so I could send you my excel so you could see what I was trying to do.. I saw you original post about vba a while back but am not sure how to set that kind of stuff up.. I know some excel but am by no means an Expert..

 

[Greg Truby]

Sounds like (with a bit of work) you could adapt Andy Pope's picture viewer solution to this. It would take a bit of work to set up, but once you had it done, you could just pick the team off of a list and the logo would change appropriately. No VBA involved, just a lot of charting slight-of-hand.

 

[Joe Was]

I think I see what is going on. Your picture is on top of a cell reference for that picture and you want to select that cell reference and AKA the picture on top of that cell based upon a condition in another cell?

If this is correct then the Change Event for that sheet will work for a redirect. If not then I still do not know what you are looking for?


This code takes the current cell value and uses it to get the right file name for a picture stored in a folder and replaces the current picture with the new one as that cell value changes.


Private Sub Worksheet_Change(ByVal Target As Range)
'Sheet Module code, like: Sheet1.
Dim myFlgSel$, myFlgFile$

'Get selected flags name only.
If Target.Address <> "$E$10" Then Exit Sub
myFlgSel = Range("E10").Value

'Test for no name found!
On Error GoTo myErr1

'Load active flags name.
myFlgFile = Range("F10").Value
'Remove current flag from sheet.
ActiveSheet.Shapes(myFlgFile).Select
Selection.Cut

myErr1:
'Use selected name to load file name.
Select Case myFlgSel
Case "United States of America"
myFlgFile = "us-s"

Case "China"
myFlgFile = "ch"

Case "Poland"
myFlgFile = "poland"

Case "United Kingdom"
myFlgFile = "uk"

Case "England"
myFlgFile = "england"

Case Else
Exit Sub
End Select

'Load selected flag file to sheet.
ActiveSheet.Pictures.Insert("U:\Excel\Test\" & myFlgFile & ".gif").Select
Selection.Name = myFlgFile
Range("F10").Value = myFlgFile

Selection.ShapeRange.ScaleWidth 0.35, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.35, msoFalse, msoScaleFromTopLeft
Application.CommandBars("Picture").Visible = False

ActiveSheet.Shapes(myFlgFile).Select
Selection.ShapeRange.IncrementLeft 2#
Selection.ShapeRange.IncrementTop 16#
Range("A1").Select
End Sub

 

[Legacy 98055]

See this example as well...

RudeBoy_PicLink.zip

 

[RudeBoy]

Awesome Tom (Right_Click).. That is going to work I think.. I just need to incorporate that logic into my current excel and it should.. work..

Because right now im cutting and pasting every year.. This will eliminate that for sure..

Thanks allot..

 

[Legacy 98055]

Hi Chris. I recieved your file but am not sure how your data is getting into your workbook. How is your data getting into columns D thru M? Directly entered by some person, by way of a query, ect...? There is going to be some legwork involved no matter how you go about this.

Here is how I would approach this using your current workbook.

You will need to create a table with the following information. This table would be created on the same worksheet as your archived logos.

LookUp Value, ArchivePictureName, DestinationWorksheetName, DestinationPictureName.. where LookUp Value is the team name such as the value in cell A4 of sheet "NHL Playoffs ROUND 1".

All of your pictures would need to be placed in their own respective single-cell range with the range being sized to the same dimensions as the picture it contains. See the example I posted earlier.

Frankly, I would not do it that way. I would load all of your logos into a single resource file and remove the "NHL & NBA Logo's" worksheet altogether. The resource file might be a simple binary file or an Access database. This would trim away about six megabytes of fat from your workbook. Each picture in this resource file would need a unique ID such as a team name. After doing this, we simply update your pictures on each sheet based upon user entry or calculation. I don't know yet because I do not know how your data actually gets into your worksheets.

If you will provide me with:
Actual disk files or links to each logo.
A unique team-name(ID) for each logo.
Answer my questions about "how" you data is beging entered into your worksheets.
I can help you out.

I also noticed that you have different logos for each team. A large one and a small one. We only need the large ones. Excel will scale it down automatically. Use a single picture, with the best resolution, for each team.

If you do not want to put this much work into this, may I post your workbook here so others can take a look and offer alternative suggestions?