pull pictures by using Vlookup?

[all4excel]

I have a two tabs
1. Picture Database
2. Reports.

In the 1st tab- Picture Database I have stored pictures in the column C from C2 till C21 and the Picture Ids are in the column B from B2 till B21.

Now is it possible for me to use something to retrieve the pictures based on their IDs, the way we normally use in Vlookup.

Like Vlookup(Picture ID,Range (ColB:C),2,false)..

please advise.

[sweep]

Hi,

You can do this by making you pictures visible and invisible dependant on a cell value. For example:

Please Login or Register  to view this content.

[all4excel]

Just one question Is it possible to do this MS Excel?
and also be able to change the reference.

In VBA also it is possible to change the reference by providing the range somewhere, the way we use INDIRECT function.?

[sweep]

Can you post your workbook? Or an example?

[all4excel]

Can you post your workbook? Or an example?

I am attaching a file..
I want the Picture to be pulled the way we use Vlookup?

Please go thru the file...

Can I have this done completely in MS Excel if possible...

And if you are doing it in VBA please explain as I dont understand VBA.

What if I cahnge the position of the Pictures in the Picture tab from column B..

allexcel..

[all4excel]

I need to use the Pictures the way we use any lookup functions.

Is that possible in MS Excel?

[vane0326]

Here is a formula version. I used define name range formulas for this.



Hope it helps!

[all4excel]

But can u please explain as to how this was achieved?

And how do i drag it down like Vlookup?

Please elaborate...

[vane0326]

But can u please explain as to how this was achieved?

And how do i drag it down like Vlookup?

Please elaborate...

I created 4 name range formulas, Like this...

Pic1 =INDEX(Sheet2!$B:$B,MATCH(Sheet1!$A$2,Sheet2!$A:$A,0))
Pic2 =INDEX(Sheet2!$B:$B,MATCH(Sheet1!$A$3,Sheet2!$A:$A,0))
Pic3 =INDEX(Sheet2!$B:$B,MATCH(Sheet1!$A$4,Sheet2!$A:$A,0))
Pic4 =INDEX(Sheet2!$B:$B,MATCH(Sheet1!$A$5,Sheet2!$A:$A,0))


To learn more about Define Name range formulas here is a link below.

http://www.contextures.com/xlNames01.html

I don't know no other way. I do understand you want to just drag the formula down like keeping it as a relative reference. But it doesn't work that way. Maybe some other more experience formula gurus on this board could do it. If not then VBA is the way to go and unfortuanly I'm not a programmer.

Hope that helps!

[all4excel]

[QUOTE=vane0326]I created 4 name range formulas, Like this...

Pic1 =INDEX(Sheet2!$B:$B,MATCH(Sheet1!$A$2,Sheet2!$A:$A,0))
Pic2 =INDEX(Sheet2!$B:$B,MATCH(Sheet1!$A$3,Sheet2!$A:$A,0))
Pic3 =INDEX(Sheet2!$B:$B,MATCH(Sheet1!$A$4,Sheet2!$A:$A,0))
Pic4 =INDEX(Sheet2!$B:$B,MATCH(Sheet1!$A$5,Sheet2!$A:$A,0))

-----------------------------------------------------------------------
As far as your formula goes it gives the required result however I would have to Define the Names for each photo which would be tedious..Is there a Drag and Drop easier way...

I am also open to change the Design /Layout of my Table..

Purpose:
Im trying to make a file on Cricket for my organisation , I would like to put a photo of each player in the main DATA file where I mention all their information..

Now I would have a TAB called Achievements where I would have a Drop-down containing a List of all Player,On selecting the name I would get all the other information by using a Vlookup Now how do I get the photos displayed next to the name of the player..

If there's any other way I can get to do this please advise even by changing the design or even by using VBA if possible but please explain...


I hope I have explained well this time...

[all4excel]

Bump Bump Bump Bump Bump Bump Bump Bump Bump Bump

[daddylonglegs]

Hello all4excel,

Sorry to impose on this thread - I tried to PM you on another matter but your inbox appears to be full, suggest you clear some old messages.....

[protonLeah]

This link may help:
http://www.ozgrid.com/forum/showthread.php?t=62575

[all4excel]

This link may help:
http://www.ozgrid.com/forum/showthread.php?t=62575

IS there a simpler way of doing tht the way we use drag and drop methods, in this we have to demarcate every picture by the number of columns and rows..?

[protonLeah]

1. Save your pictures to a new folder on some drive
2. Insert a new module and insert the following code:
   

   Option Explicit 
   Public PicArray() As String  

3. Next, in the ThisWorkbook module, insert:
   

   Option Base 1 
   Private Sub Workbook_Open() 
         Dim fs As Object, i As Integer 
         Set fs = Application.FileSearch 
         With fs 
               .LookIn = "C:\dbpix"       '<========== replace w/ your folder name' 
               .SearchSubFolders = True 
               .Filename = "*.jpg"         '<===== or *.gif, etc' 
               If .Execute() > 0 Then 
                     ReDim PicArray(1 To .FoundFiles.Count) 
                     For i = 1 To .FoundFiles.Count 
                            PicArray(i) = .FoundFiles(i) 
                     Next i 
               End If 
         End With 
   End Sub  

4. Finally, in the code module for the "Pull" sheet:
   
   

   Private Sub Worksheet_Change(ByVal Target As Range) 
          Dim SerialNos As Range, PicNum As Integer,  isect, Pic_URL As String 
           
          Set SerialNos = Range("A:A")     'you could add code to find the last row' 
          Set isect = Application.Intersect(SerialNos, Target) 
          If Not isect Is Nothing Then 
               PicNum = Target.Value 
               Pic_URL = PicArray(PicNum) 
               Target.Offset(0, 2).Select 
               ActiveSheet.Pictures.Delete      '<====  delete any current picture' 
               ActiveSheet.Pictures.Insert(Pic_URL).Select 
         End If 
   End Sub  

Suggestion:
If you have less than 100 pics, you should name the picture files "01-Blinky, 02-nnnn,...,99-nnnn, in the order YOU want them so that the file system doesn't order them alphabetically.

[all4excel]

Thanks for your efforts

I will check and let you know..

I need to collect some pictures for the same..

However Is it possible by using some formulas?

all4excel.

[protonLeah]

I may be wrong, but, I don't think it's possible to access graphics with formulas in cells.

It's my understanding that graphics are on a separate layer "above" the cells, not "in" the cells as are the values.

So, for example, I you can't insert graphics in column B of a table and use Vlookup of a value in column A and have it fetch the graphic.

The thought occurs to me that a table of URL's (path's to a folder, in this case), that might work with VLOOPUP, but...(?)