How can multiple different hyperlinks be given to corresponding cells quickly?????

[amrinderminhas]

Hello everyone...
I have multiple number of 'excel' files at present in a folder as shown in figure 1 (in some situations the files can also be pdf or jpg) which i want to get hyperlinked to a new excel file as shown in Figure 2. Now i need that in Figure 2, in Column A (Where there is written Experiment 1, Experiment 2, Experiment 3 and so on...), each cell be hyperlinked with corresponding excel file quickly, instead of me selecting each cell and hyperlinking it with the excel file for all files.
Kindly make the arrangement such that the hyperlinked file can be of any format (i.e.. word, pdf, jpg, avi, mpeg etc.)

Figure1



Figure2

 

[NoS]

Hello amrinderminhas

If I understand what you are after and a VBA solution will suffice, you could try a macro like this

Sub Assign_Hyperlinks()
'http://www.excelguru.ca/forums/showthread.php?2837

    Dim rng As Range
    Dim cel As Range
    Dim LastRow As Long
    Dim Path As String
    Dim FileName As String
    
With Sheet1

    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set rng = Range("A2:A" & LastRow)
    
    Path = "D:\New folder"   '<-- put your directory here

    If Right(Path, 1) <> Application.PathSeparator Then     'if path doesn't end in "\"
        Path = Path & Application.PathSeparator                  'add "\"
    End If
    
    For Each cel In rng
    
        FileName = Dir(Path & cel.Value & ".*", vbNormal)
                 
        If FileName <> "" Then cel.Hyperlinks.Add anchor:=cel, Address:=Path & FileName
            
    Next cel
    
End With

End Sub

Hope that helps
Good luck
NoS