List File Names Using Formula

Hi All

Here is a method to list the files names from a directory.

Write down the directory name in a cell (for example in E1 on Sheet1 : C:\My Documents\). Don't forget to add '\' at the end.
Then define a Name using Name Manager.

Hit CTRL+F3 to open the Name Manager dialogbox:

1. Click on New
2. Give a name (for example PDF_Files) in the Name box
3. In the Refers to box, type =FILES(Sheet1!$E$1&"*.PDF"). (Replace '.PDF' with the file extension you want)
4. Click OK.

Now enter this formula in A2 and copied down,

=IFERROR(INDEX(PDF_Files,ROWS($A$2:A2)),"")

If you want to hyperlink these files, use the following folrmula

=IFERROR(HYPERLINK($E$1&INDEX(PDF_Files,ROWS($A$2: A2)),INDEX(PDF_Files,ROWS($A$2:A2))),"")