Mr Excel & excelisfun Trick 42: Lookup Picture In Excel Formula or VBA?

See Mr Excel and excelisfun lookup a picture in Excel. See a VBA solution and a Formula Solution using the INDIRECT function and named ranges. See many tricks in this video:
1.Data Validation List
2.Hiding Text with formatting
3.Named Ranges
4.Keyboard shortcut for Naming Create Name From Selection
5.Keyboard shortcut for Pasting a Name
6.Insert A Comment
7.Format a Comment (click outer edge)
8.Insert Picture in Comment
9.VLOOKUP
10.VBA to lookup a picture
11.Keyboard shortcut for VBA editor
12.Defined Name Formula
13.INDIRECT function to convert text that represents a reference into a reference
14.Put a formula in a picture
15.Paste Special Linked Picture (Camera Tool)
16.Checking Defined Name accuracy with Collapse Button
17.More
How do I Lookup a Picture In Excel VLOOKUP Lookup picture in Database using Excel How do I use VLOOKUP to retrieve a picture

Category:

Science & Technology

Tags:

• Excel
• Lookup
• Picture
• Formula
• or
• VBA
• INDIRECT
• function
• VLOOKUP
• picture
• How
• do
• use
• to
• retrieve
• Database
• excelisfun
• Bill
• Mr
• Jelen
• Mike
• Gel
• Girvin
• Highline
• Community
• College
• in
• using

[Sherlock69Holmes]

hey Mr Excel.., am not getting words for how should i appreciate . Its awesome and thanks a ton for sharing these tricks. found these sessions , extremely helpful and customized as per my requirement and completed a small project on it .

Thanks Again Mr Excel

[ExcelIsFun]

@Sherlock69Holmes , you are welcome!

[larrym14]

I found a link to this by doing a google lookup using this as the lookup value: mrexcelexcelisfuntrick42 filetype:xls

[ExcelIsFun]

@larrym14 , that is amazing! However, I thought Google stopped supporting GoogleLookup as of nov 2011...

[RoderickFred]

With a small change I FINALY got this to work for my self. It may be opperator error but I changed Bill's MR. EXCEL code to this and got it to work:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$6" Then NewPic = "C:\aaa\pics\" & Range("c7").Value Target.Comment.Shape.Fill.User­Picture PictureFile:=NewPic End If

End Sub

[ExcelIsFun]

@Sherlock69Holmes , I am excelisfun, not Mr Excel. Mr Excel did the VBA. I am not so good with VBA. However, it should work in a workbook with the extension .xlsm

[Sherlock69Holmes]

@farouk450

Hi , does this VBA worked , it worked for me exactly as shown . but when i closed it and opened again , picture does not change dynamically with the name.

what format does i need to save my file ?? please help

[Sherlock69Holmes]

Mr Excel , Have Implemented the excel trick and it worked as shown . But when i close , got a pop-up to save as excel Macro enabled workbook . so i did save as Book.xlsm format.

when Opened , still i can see VB logic , all details , but picture does not change.

Please help me , do i need to take care of any other thing as part of trick.

[tomboi1978]

Is the reason the second trick doesn't work in excel 2007 because you can't add a formula to the picture itself? I was doing this in excel 2010 for a while and only found out excel 2007 wouldn't do it when I tried to create a new sheet for a co-worker. I got stopped when trying to enter the formula into the picture. It wouldn't let me type anything in the formula bar with the pic selected. If I opened the 2010 version in 2007 it worked. I just couldn't create a new one from scratch.