Need some input from excel expert's.

I need to make a script in excel that
would search and insert location(address) of the file in column.

Such as if I insert file name : xyz (to
be searched) in A column it would generate the link in B column

Something like search function in
windows.

Is this possible in excel?

Thanks.

you need to write vba code sth like this:

Createobject FileSystemObject say fso.

get file name using GetFileName method of fso

iterate through the returned file names and assign it to the column B cell

Alternatively u can just use Dir(path) function similarly without creating fso object.

U need to put this code in the change event of the worksheet.
Also to prevent any other change in the worksheet to trigger above action,
do a intersection check of the changed range and the target range of cells.

You mean if you type "wheels" in column A1 it should appear in B1?

like:

A	B
wheels	wheels

Go to column B and type =A1 (what ever you have on column A1 will appear there)

Or if you want  to import sheet1(table) to sheet 2 table anywhere
copy the table (data) from Sheet1 and paste in Sheet2 and make sure you use link on the drop down..

it will look like this....      =Sheet1!$A$2

Unless I am not understanding your statement....

Here you go. This VBA function is a function that I use in one of my VBA programs. All I had to do was modify it slightly for your purposes. All you have to do is simply call the function like:
searchFile "*.txt", "c:\", False

Hope this helps



'Returns 1 on success           0 on Failure
'You can use wildcards also in the filename.
'   Ex: *.txt
'
'Note:     Don't do: searchFile "*.txt", "c:\", True  (Your computer might hang)
Function searchFile(file_name As String, path_to_search As String, look_in_sub_folders As Boolean)
    Dim perr, res_row_no, res_row_col
    Dim File_Path As String, out_file As String, title1 As String, add_extension As String, res_sheet_name As String
    Dim No_Of_Files As Integer, i As Integer
    
    On Error GoTo Err_searchFile
    title1 = "File Search v1.0"
    
    '**********************************************
    'You can modify these values
    '**********************************************
    res_sheet_name = "Sheet3"       'If you are in a different sheet, simply put the sheet name here
    res_row_no = 1  'Give the starting row number where you want the results displayed
    res_row_col = "a"   'Give the column where you want the results to be displayed
    '**********************************************
    '**********************************************

    'Don't modify
    res_row_col = Asc(UCase(res_row_col)) - 65 + 1

    'Search for the file
    With Application.FileSearch
        .NewSearch
        .LookIn = path_to_search
        .filename = file_name
        .SearchSubFolders = look_in_sub_folders
        .Execute
    
        No_Of_Files = .FoundFiles.Count
        If No_Of_Files <= 0 Then
            MsgBox "Sorry couldn't find " & file_name & " in " & path_to_search & ".", , title1
            GoTo failed_exit
        Else    'If the file(s) was found
            For i = 1 To No_Of_Files
               Worksheets(res_sheet_name).Cells(i + res_row_no - 1, res_row_col).Value = .FoundFiles(i)
            Next i
        End If
    End With
    searchFile = 1      'Success code
    Exit Function       'Sucessful exit of the function

Err_searchFile:
    perr = Err
    MsgBox "ERROR! Inside searchFile. " & Err.Description, , title1

failed_exit:
    searchFile = 0
End Function