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.

??? · Oct 14, 2009 8:17 AM

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

hitmeonce · Oct 14, 2009 10:31 AM

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

garbagegigo · Oct 17, 2009 10:19 AM