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

 

[hady]

simplify Tom's metode

by only using names and index function no VBA required, this can be done also. here is the trick :
1. use sheet from tom (right click) to demonstrate and delete all the formula and VBA
2. on sheet displayedpic, click on blank picture
3. insert new define name "test" with formula
=INDEX('NHL & NBA Logos'!$A:$A,DisplayedPics!$C$6)
4. while your selection still in blank picture, type in formula
=test, press enter
5. Now you can type 1 - 3 in cell C6 0n sheet displayedpic and the pic change as index function without any VBA just native worksheet function

hady
Xl-mania

 

[hady]

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.

I disagree with your opinion check this link regarding my earlier explanation

http://rapidshare.com/files/14354730/RudeBoy_PicLink.rar.html

 

[vane0326]

Re: simplify Tom's metode

by only using names and index function no VBA required, this can be done also. here is the trick :
1. use sheet from tom (right click) to demonstrate and delete all the formula and VBA
2. on sheet displayedpic, click on blank picture
3. insert new define name "test" with formula
=INDEX('NHL & NBA Logos'!$A:$A,DisplayedPics!$C$6)
4. while your selection still in blank picture, type in formula
=test, press enter
5. Now you can type 1 - 3 in cell C6 0n sheet displayedpic and the pic change as index function without any VBA just native worksheet function

hady
Xl-mania

Nice!!!! , That is a neat trick.

 

[Greg Truby]

Nicely done, Hady. It's rare that anyone can improve on Tom's work. And to do it on your 3^rd post? "Welcome to the board!" is all I can say.

 

[Legacy 98055]

Example here from an earlier post...

Need to link cell value to display embedded object

UpdatePicUsingWorkbookName.zip

 

[grantholt]

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

Hey Joe, could you please help me with your code from a few years back?

I searched the forum and got this, and this is the one that I really want to use.. but I dont know where to edit.

I edited the file where my logos are: they are in - C:\Documents and Settings\Ashleigh1\My Documents\ontheball-l2-08

I also edited the case scenarios to what would be written in my cell and my file names.. but its still not working and I'm not sure where to look. There is no bug or anything in the code as it doesn't need debugging, but no picture comes up in the cell.. Did I need to put something there?

 

[Joe Was]

In my code Cell E10 is an in-cell-dropdown from Validation. The Select Case statement converts the dropdown choices that the user sees to the correct file's name. Then the code goes to the Folder and gets the correct file to replace the current one. You may need to load a default picture in the display cell.