Get the List of File Names from a Folder in Excel (with and without VBA)

On my first day in my job in a small consulting firm, I was staffed on a short project for three days.

The work was simple.

There were many folders on the network drive and each folder had hundreds of files in it.

I had to follow these three steps:

  1. Select the file and copy its name.
  2. Paste that name in a cell in Excel and hit Enter.
  3. Move to the next file and repeat step 1 & 2.

Sounds simple right?

It was – Simple and a huge waste of time.

What took me three days could have been done in a few minutes if I knew the right techniques.

In this tutorial, I will show you different ways to make this entire process super fast and super easy (with and without VBA).

Limitations of the methods shown in this tutorial: With the techniques shown below, you will only be able to get the files names within the main folder. You will not get the names of the files in the sub-folders within the main folder. Here is a way to get names of files from folders and sub-folders using Power Query

Using FILES Function to Get a List of File Names from a Folder

Heard of FILES function before?

Don’t worry if you haven’t.

It is from the childhood days of Excel spreadsheets (a version 4 formula).

While this formula does not work in the worksheet cells, it still works in named ranges. We will use this fact to get the list of file names from a specified folder.

Now, suppose you have a folder with the name – ‘Test Folder‘ on the desktop, and you want to get a list of file names for all the files in this folder.

Here are the steps that will give you the file names from this folder:

  1. In cell A1, enter the folder complete address followed by an asterisk sign (*)
    • For example, if your folder in the C drive, then the address would look like
      C:\Users\Sumit\Desktop\Test Folder\*Folder address in a cell
    • If you are not sure how to get the folder address, use the following method:
      • In the folder from which you want to get the file names, either create a new Excel Workbook or open an existing workbook in the folder and use the below formula in any cell. This formula will give you the folder address and adds an asterisks sign (*) at the end. Now you can copy paste (paste as value) this address in any cell (A1 in this example) in the workbook in which you want the file names.
        =REPLACE(CELL("filename"),FIND("[",CELL("filename")),LEN(CELL("filename")),"*")

        [If you have created a new workbook in the folder to use the above formula and get the folder address, you may want to delete it so that it doesn’t feature in the list of files in that folder]
  2. Go to the ‘Formulas’ tab and click on the ‘Define Name’ option.File Names from a Folder in Excel - Define Name
  3. In the New Name dialogue box, use the following details
    • Name: FileNameList (feel free to choose whatever name you like)
    • Scope: Workbook
    • Refers to: =FILES(Sheet1!$A$1)File Names from a Folder in Excel - Define Name Refres to
  4. Now to get the list of files, we will use the named range within an INDEX function. Go to cell A3 (or any cell where you want the list of names to start) and enter the following formula:
    =IFERROR(INDEX(FileNameList,ROW()-2),"")
  5. Drag this down and it will give you a list of all the file names in the folder

Getting the File Names from a folder using the FILES function Excel

Want to Extract Files with a Specific Extension??

If you want to get all the files with a particular extension, just change the asterisk with that file extension. For example, if you want only excel files, you can use *xls* instead of *

So the folder address that you need to use would be C:\Users\Sumit\Desktop\Test Folder\*xls*

Similarly, for word document files, use *doc*

How does this work?

FILES formula retrieves the names of all the files of the specified extension in the specified folder.

In the INDEX formula, we have given the file names as the array and we return the 1st, 2nd, 3rd file names and so on using the ROW function.

Note that I have used ROW()-2, as we started from the third row onwards. So ROW()-2 would be 1 for the first instance, 2 for the second instance when the row number is 4, and so on and so forth.

Watch Video – Get List of File Names from a Folder in Excel

Using VBA Get a List of All the File Names from a Folder

Now, I must say that the above method is a bit complex (with a number of steps).

It’s, however, a lot better than doing this manually.

But if you’re comfortable with using VBA (or if you’re good at following exact steps that I am going to list below), you can create a custom function (UDF) that can easily get you the names of all the files.

The benefit of using a User Defined Function (UDF) is that you can save the function in a personal macro workbook and reuse it easily without repeating the steps again and again. You can also create an add-in and share this function with others.

Now let me first give you the VBA code that will create a function to get the list of all the file names from a folder in Excel.

Function GetFileNames(ByVal FolderPath As String) As Variant
Dim Result As Variant
Dim i As Integer
Dim MyFile As Object
Dim MyFSO As Object
Dim MyFolder As Object
Dim MyFiles As Object
Set MyFSO = CreateObject("Scripting.FileSystemObject")
Set MyFolder = MyFSO.GetFolder(FolderPath)
Set MyFiles = MyFolder.Files
ReDim Result(1 To MyFiles.Count)
i = 1
For Each MyFile In MyFiles
Result(i) = MyFile.Name
i = i + 1
Next MyFile
GetFileNames = Result
End Function

The above code will create a function GetFileNames that can be used in the worksheets (just like regular functions).

Where to put this code?

Follow the steps below to copy this code in the VB Editor.

  • Go to the Developer tab.Developer tab in the ribbon
  • Click on the Visual Basic button. This will open the VB Editor.Visual Basic button in the ribbon
  • In the VB Editor, right-click on any of the objects of the workbook you’re working in, go to Insert and click on Module. If you don’t see the Project Explorer, use the keyboard shortcut Control + R (hold the control key and press the ‘R’ key).insert Module in VB Editor
  • Double click on the Module object and copy and paste the above code into the module code window.Copy code in module to get the file list name from a folder

How to Use this Function?

Below are the steps to use this function in a worksheet:

  • In any cell, enter the folder address of the folder from which you want to list the file names.
  • In the cell where you want the list, enter the following formula (I am entering it in cell A3):
    =IFERROR(INDEX(GetFileNames($A$1),ROW()-2),"")
  • Copy and paste the formula in the cells below to get a list of all the files.

Get List of File Names from Folder using VBA function

Note that I entered the folder location in a cell and then used that cell in the GetFileNames formula. You can also hard code the folder address in the formula as shown below:

=IFERROR(INDEX(GetFileNames("C:\Users\Sumit\Desktop\Test Folder"),ROW()-2),"")

In the above formula, we have used ROW()-2 and we started from the third row onwards. This made sure that as I copy the formula in the cells below, it will get incremented by 1. In case you’re entering the formula in the first row of a column, you can simply use ROW().

How does this formula work?

The GetFileNames formula returns an array that holds the names of all the files in the folder.

The INDEX function is used to list one file name per cell, starting from the first one.

IFERROR function is used to return blank instead of the #REF! error which is shown when a formula is copied in a cell but there are no more file names to list.

Using VBA Get a List of All the File Names with a Specific Extension

The above formula works great when you want to get a list of all the file names from a folder in Excel.

But what if you want to get the names of only the video files, or only the Excel files, or only the file names that contain a specific keyword.

In that case, you can use a slightly different function.

Below is the code that will allow you get all the file names with a specific keyword in it (or of a specific extension).

Function GetFileNamesbyExt(ByVal FolderPath As String, FileExt As String) As Variant
Dim Result As Variant
Dim i As Integer
Dim MyFile As Object
Dim MyFSO As Object
Dim MyFolder As Object
Dim MyFiles As Object
Set MyFSO = CreateObject("Scripting.FileSystemObject")
Set MyFolder = MyFSO.GetFolder(FolderPath)
Set MyFiles = MyFolder.Files
ReDim Result(1 To MyFiles.Count)
i = 1
For Each MyFile In MyFiles
If InStr(1, MyFile.Name, FileExt) <> 0 Then
Result(i) = MyFile.Name
i = i + 1
End If
Next MyFile
ReDim Preserve Result(1 To i - 1)
GetFileNamesbyExt = Result
End Function

The above code will create a function ‘GetFileNamesbyExt‘ that can be used in the worksheets (just like regular functions).

This function takes two arguments – the folder location and the extension keyword. It returns an array of file names that match the given extension. If no extension or keyword is specified, it will return all the file names in the specified folder.

Syntax: =GetFileNamesbyExt(“Folder Location”,”Extension”)

Where to put this code?

Follow the steps below to copy this code in the VB Editor.

  • Go to the Developer tab.
  • Click on the Visual Basic button. This will open the VB Editor.
  • In the VB Editor, right-click on any of the objects of the workbook you’re working in, go to Insert and click on Module. If you don’t see the Project Explorer, use the keyboard shortcut Control + R (hold the control key and press the ‘R’ key).
  • Double click on the Module object and copy and paste the above code into the module code window.

How to Use this Function?

Below are the steps to use this function in a worksheet:

  • In any cell, enter the folder address of the folder from which you want to list the file names. I have entered this in cell A1.
  • In a cell, enter the extension (or the keyword), for which you want all the file names. I have entered this in cell B1.
  • In the cell where you want the list, enter the following formula (I am entering it in cell A3):
    =IFERROR(INDEX(GetFileNamesbyExt($A$1,$B$1),ROW()-2),"")
  • Copy and paste the formula in the cells below to get a list of all the files.

Get File Names from a Folder in Excel by Extension keyword

How about you? Any Excel tricks that you use to make life easy. I would love to learn from you. Share it in the comment section!

You May Also Like the Following Excel Tutorials:

  • Jac says:

    I only tried the first method and it works perfectly for me… thank you so much for saving me days of boring inputing!!

  • Jenivive says:

    How to get the file name list in Date Modified order in this excel workbook ??

  • MrBing says:

    Umm its not working on MAC 🙁
    The formula you’ve provided (in column A) gives me this:
    /Volumes/Data/Reports/*

    So the INDEX formula (in column B) gives #N/A
    🙁

  • Shweta says:

    I used the code above to obtain a list of files. The files names are as below:
    Diesel___1234567___NIR_cuvette___20180912_234811.0
    Diesel___1234567___NIR_cuvette___20180912_235510.0
    The code only pulls the first file for each sample and fails to list the second (or third file). Is there a way to correct for this? Thanks.

  • MYK says:

    Hey,
    I have a ecxel sheet which have some product names, and also have a folder which have some pdf files named same as in cell data, like if cell A2 value is apple1, Pdf file name is apple1.pdf, i want to know which name file is missing, can we get that in excel somwhow..

  • BlueVirus says:

    Hi Sumit,

    I want to list the names and duration of all videos in a folder and its subfolders using Excel VBA. From the code below I can get the duration of video files, but I can’t exclude all other files. This gives me a list of all the file names, which I don’t need. Also I am failing to loop through subfolders.
    What I want to achieve is for the macro to loop through all subfolders in the the given root folder and list only video names and duration in columns A and B.
    Some help with this is truly appreciated.
    Option Explicit

    Dim i As Long, SourceFldr
    Dim c As Range, rng As Range
    Dim sFile As Variant
    Dim oWSHShell As Object
    Dim WS As Worksheet
    Dim lRow As Long

    Sub GetDuration()

    Dim fldr As FileDialog
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    Set oWSHShell = CreateObject(“WScript.Shell”)

    With fldr
    .Title = “Select a Source Folder”
    .AllowMultiSelect = False
    .InitialFileName = oWSHShell.SpecialFolders(“Desktop”)
    If .Show -1 Then GoTo NextCode
    SourceFldr = .SelectedItems(1)
    NextCode:
    End With

    Dim oShell: Set oShell = CreateObject(“Shell.Application”)
    Dim oDir: Set oDir = oShell.Namespace(SourceFldr)

    i = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Row + 1

    For Each sFile In oDir.Items
    Cells(i, 1).Value = oDir.GetDetailsOf(sFile, 0) ‘File Name
    Cells(i, 2).Value = oDir.GetDetailsOf(sFile, 27) ‘File Lenght
    i = i + 1
    Next sFile

    Set oDir = Nothing
    Set oShell = Nothing

    End Sub

  • benj says:

    what about this one? I really need it 🙁 hope you can help
    https://www.reddit.com/r/vba/comments/7g3el8/catalog_files_from_folderssubs_using_excel_vba/

  • Zubair Abbas says:

    Wow! That’s ingenious and too much for free 😀 May Allah bless you brother. Thanks a lot.

  • Hey! Sumit Bansal right? As a matter of fact you are my excel HERO. I’ve been following your blog for quite a while now and everything you thought was amazing. Even though I’m still a student, I know one day this knowledge is going to save me a lot of time. I thought I was an excel expert with my one semester training So I created a blog to publish my skills on (http://excel-programming.com). But upon discovering your skills and experience I think I still have a a long way to go. Thank you very much for this blog.

  • Sundaresh says:

    You are doing wonderful work Sumit to educate Excel users. God bless you.
    Please try the Excel Addin called ASAP Utilities by downloading it. There is a free (Home&Student) and paid version. File listing in Excel sheet of any directory and nested sub directories, with many properties of the files is so easy with many menu driven options (Menu-File & System-Item 24) This is just one of more than 300 utilities. It will be very useful for all Excel users and saves tons of time and effort. Current version is 7.4 and the link to the site is http://www.asap-utilities.com/ . I do not have any pesronel intrest in the product except to make it known to many Excel users to benifit in their work. It was developed by Bastien Mensink from Netherlands way back in 1999. I am using it since that time. Feed back on your experience is appreciated.

  • Shyam Sharma says:

    Sumit this one deserves a kudos!!

    Some time back i was working on its VBA and m non VBA guy……….

  • Charles McDermott says:

    I was also wondering if there is a way to extract additional properties information at the same time
    ie
    “File Name”
    “Created”
    “Owner”
    “Author”
    “Title”
    “Comments”
    “Tags”

  • umesan says:

    nice. really interesting. thanks a lot

  • GraH says:

    Hmm, nowadays I would go with a Power Query ( Get and Transform) solution. Read from folder, and delete all columns except file name. Save and load to table. No macro, no formula involved.

  • Sandy K says:

    Thank you for this repost. Is there a way to list folders in a directory?

    • Sandy K says:

      I just saw a post below that this function does not work with folders. Too bad 🙁

  • Charles says:

    Since it has not been corrected I assume that it has not been reported yet. The first version of this formula: =IFERROR(INDEX(FileNameList,ROW()-2,””) should actually be: =IFERROR(INDEX(FileNameList,ROW()-2),””). The trick won’t work until the right formula is used.

    • Sumit Bansal says:

      Thanks for pointing out Charles.. have corrected it!

  • prasanna kumar says:

    Hi Sumit, Great trick. Thanks a lot. However I was wondering if there is a way to extract the file path as well along with the file name

  • Bond says:

    I copied everything exactly but my cells are blank in the B column and it doesn’t populate the file names. any reason why? Also I’m using office 2010.

    • Bond says:

      I figured out the issue, i didn’t have a slash before the asterisk * at the end. But I have one more question. Can this be used to get a value from a cell in these docs as well? e.g. I get a list of all docs in a given folder, can I then get a value from a cell in each of those docs if its all the same cell in each doc?

  • KWiki says:

    Hi
    Was wondering if there is a way to extract properties information at the same time
    ie
    “File Name”
    “Created”
    “Owner”
    “Author”
    “Title”
    “Comments”
    “Tags”

    Much appreciated

  • nenwtoexcel says:

    Hi Sumit, great tutorial. I used this because files are constantly being added to a specific folder. This allows for the names of those new folders to show. Since I do not know the names of the new files that will be created I was hoping to then use the results of this in an external reference formula. Do you know if this is possible?

  • Francesco Bolleri (Frank Bolle says:

    Please take a look at https://isystems-it.azurewebsites.net/2017/04/23/fs-utilities/
    for a more comfortable approach.

  • Guest says:

    One method I have used before uses the command prompt. Navigate to the folder you want to extract file names from. Type (dir /b > “sample.txt”) minus the parenthesis. This will create a text file in the same directory that you can then open in Excel for further processing.

  • Mohammed Bsatee says:

    how to have excel list all the file in one row instead of using multiple rows

  • Shah Khalid says:

    dear sir,
    how can i Get a List of File Names from a Folder in Excel without extension like . jpg, .pdf

  • Kristy Dole says:

    Amazing! Thanks for this Great Trick!!!

    Question: When the New File Names come in, they Start Over from the Top-Shifting File Names Down, how can I get them to come in at the bottom of the list (based on date/time modified)?

    Make File Name Hyperlink? If I Select the Column with your Formula, Insert Hyperlink and Add Folder Location, this links them to the folder, but how can I make it open the file directly? Also If you can Help: I have a Folder Filled with Email Messages (.msg Files) that I am keeping a Running List of in Excel and have to manually enter data from each Email such as Name (Email Address before @) + Company (Email Address after @), Date Received etc. – Is there a way to Auto Populate this information into Excel from the .msg File following the Automated File Name you have created here?

  • t dobbs says:

    I have a single folder with multiple sub-folders each with multiple files, can I extract at the highest folder level?

  • Nishath says:

    Hi, Is there a way i can get the time the file is created in addition to the file name?

  • Naghman Munir says:

    Hei Sumit. I got Folder name in A1. But INDEX will not work properly, I get only #N/A, (I define A1 as “NM”)
    Can you plz look at screenshot and give me some guide lines that where i do wrong?
    And my required folder is on Sharepoint.

  • Husen Kabeer says:

    Amazingly quick response. I will re-check. Thank you.

  • Husen Kabeer says:

    I tried Getting a List of File Names from a Folder in Excel. Why did I get #NAME? instead of the name of the first file? I like your video lesson. Thank you.
    Husen Kabeer, [email protected]

    c:This PCDocumentsHusen Data Files – 2014Word*
    =INDEX(FileNameList,1)
    #NAME?

    • Sumit Bansal says:

      Hello Husen.. Check the named range reference. It seems your named range is not referring to the cell that has the folder path.

  • ehsanullah says:

    Thank you very much. This is amazing ..:)

  • David says:

    Sumit, is there a way for this formula to look within a series of sub-folders for the same results.

    • Sumit Bansal says:

      Thanks for commenting David.. With this method, you can only get file names from a specified folder

  • Brenda D says:

    Hi, Sumit
    Googled for a few minutes just now. I love your work. As a newbie you’ve helped me so much but for the life of me I just couldn’t get this working. Found an alternative method that actually lists the file names as hyperlinks. Thought I’d share the link here, in case you or your other fans/followers were interested –

    http://www.extendoffice.com/documents/excel/627-excel-list-files.html

  • camelofjoe says:

    if this done with a folder that gets updated a lot, will this auto update with the new file names or will you have to start all over

    • Sumit Bansal says:

      It would automatically update if you open the workbook or you press F9 (to force a calculation), or even if you make any change in the worksheet.

      • camelofjoe says:

        Thanks, its working just the way that i was wanting it to

      • Horbaniuc Vlad says:

        Hello Sumit, thank you for your post. Made my life easier. Never the less I still have a problem with the update. It does not update automatically. I have to drag the formula again each time I open the document, or either double click the cell to updated itself. Do you know what I might do wrong? For your information I used your formula in combination with other formulas as bellow :

        =LEFT($B$3;LEN($B$3)-1)&IFERROR(INDEX(FileList0916;ROWS($B$4:B33));””)

  • Brenda D says:

    Hi. I have been here for more than an hour and for some reason I can’t get this to work. Know I’m going to love it and use it a lot, once I can get the first one working. Any chance you can take a look at mine and tell me what I’m doing wrong? I would so much appreciate it!

    https://www.dropbox.com/s/ex6rtxpgr2twyne/Excel%20Index.xlsx?dl=0

    • Sumit Bansal says:

      Hello Brenda. You have created a named range with the name “ExcelList”, while the formula uses “FileNameList”. Change the formula to =IFERROR(INDEX(ExcelList,ROWS($B$1:B1)),””)

      • Brenda D says:

        Thanks so much for replying. And so quickly! Awesome. Unfortunately that didn’t work.

        https://www.dropbox.com/s/ebd0bbh1exwkw8y/Excel%20Index.xlsx?dl=0

        • Sumit Bansal says:

          Are you using the formula to get the folder address. Use this formula =REPLACE(CELL(“filename”),FIND(“[“,CELL(“filename”)),LEN(CELL(“filename”)),”*”)

          It shouldn’t look something as shown in your spreadsheet. Also, make sure the excel file (in which you are extracting the file names) is saved in the same folder.

          • Brenda D says:

            Morning,
            At it again this am. Losing my mind. Want this so bad and I just can’t get it to work. Tried everything. Must be something really small and stupid hanging me up. Heading to work. Little bit OCD – lol. I will get back at it when I get home but not too optimistic.

  • Emz Pepz says:

    hi sumit! i followed the instructions but all i got was a blank cell..

  • Ebrahim says:

    Hi
    The function of FILES does not exist in my version of excel 2010! May be it originated from some Add-Ins?

  • sj_1988 says:

    That is an awesome way. Thanks a lot!!…
    Also, is there a way to get the list of all the folders,subfolders and filenames along with file size and modification date columns.

    • Sumit Bansal says:

      Thanks for commenting.. Using this method, you can only get file names from a specified folder

  • Goncalo Monteiro says:

    Another way to get the directory.
    Portuguese version of formula =INFORMAÇÃO(“DIRECTÓRIO”)
    I guess in English will be =INFO(“DIRECTORY”)
    Even easier!

  • Bharat Ramachandran says:

    Hi Sumit, thanks a lot for that.. is there any way I can also get the tabs within each excel file that I am looking up in a drive to populate in the columns next to file names? Please let me know it will be really helpful

    • Sumit Bansal says:

      Hi Bharat. You won’t be able to get tab names using the FILES function. For that, you would need to resort to a VBA code

      • Bharat Ramachandran says:

        HI Sumit, thanks for the quick response. is there anyway you can help with that code? I have been trying to search for it online but nothing seems to pop up.

  • Victor Momoh says:

    Never seen this trick before. Great stuff.

    i think this formula should also work for retrieving the file path, looks shorter 🙂
    =LEFT(CELL(“filename”),FIND(“[“,CELL(“filename”))-1)&”*”

  • elisa says:

    I was able to follow your instructions, but when i save it and go back it isnt there it just has name#. I am not familiar with Macros and it ask me to save Macros-Free and when I do my list isnt there. how can I save it. apologize in advance.

    • Sumit Bansal says:

      Hi Elisa.. Thanks for commenting. Try and save your file as a macro-enabled workbook (with .xlsm extension) and it would work. Since FILES is an old macro formula, it requires the workbook to be saved in .XLSM format. And don’t worry about not knowing macros, it would still work

  • bewater says:

    Awesome solution thank you! However I could only register 256 files (rows). After that I get #¡REF!. Do you know a way to make it work for larger number of files. I need it for 2.000 files aprox. Thank you again. Jacobo

    • Sumit Bansal says:

      Thanks for commenting.. Could you share the sample file you are using. Since FILES is an old formula it may have some limitations, need to check on it.

  • Ervin Gegprifti says:

    Once I wanted to do the same so I wrote an Excel Add-in for doing that.

    It can get filenames, folders, file extensions and other information regarding files.

    This tool can write up to excel limit number of rows in just a few minutes.

    In a stress test I did, I got more than 1,000,000 file names in just about 3 minutes.

    Here is a link to try:

    http://excel.gegprifti.com

  • Narayandatta says:

    Dear Sumit,
    Suppose i want to do same for folder name than how we can do?

    • Narayandatta says:

      Hi, I am waiting for your revert.

      • Sumit Bansal says:

        Hi Narayandatta.. FILES can not be used to get the folder names within in folder. It only works for files that have an extension

  • Teak says:

    This was a great time saver Sumit, thanks. ? is there a way to make them a hyperlink without going through every single one?
    Thanks

  • manju says:

    fantastic

    • Karthik L says:

      A formula that only works in named range?!!..this is great find.

  • Peter Stratton says:

    That is really useful, thanks very much!

  • MF says:

    Interesting.

    Before dragging down, we may use

    =COUNTA(FileNameList)

    to get a sense of how far we need to go down.

    btw, another approach in getting the directory for consideration.

    =REPLACE(CELL(“filename”),FIND(“[“,CELL(“filename”)),LEN(CELL(“filename”)),”*”)

  • Daniel Dion says:

    Thanks this will be useful
    We can also do the same thing with Power Query

  • jswensson says:

    Wow, this is great. Easier than the VBA code I wrote to do the same thing!

  • torkunc says:

    you save my life.

    thnx a lot for such a great trick.

    • Sumit Bansal says:

      Thanks for the comment. Glad you liked it 🙂

      • torkunc says:

        Dear Sumit,

        Is “Files” function is valid only Excel 2013.

        I am trying to make it in Excel 2010 but could not find the Turkish of “Files” in Excel?

        Do you have an idea?

      • torkunc says:

        Dear Sumit,

        I am dying here to try above trick but still waiting your reply about the function FILES ?
        🙁

        • Sumit Bansal says:

          Hey.. this formula is valid for all versions of Excel. However, I could not find its equivalent for Turkish Excel. Since it’s an old function, even the help is not available for it now.

          • torkunc says:

            Dear Sumit,

            Thnx for your reply.

            I made it but unfortunately when I drag down, it gives only the first file name ?

            Do you have any idea?

          • Sumit Bansal says:

            It seems the second argument of INDEX is not changing. Can you try changing it to see if it work. Also, would be great if you could share the file (a link to dropbox or onedrive)

          • torkunc says:

            Hi again,

            I changed all names, but stll same…

            Here it is:

            https://www.dropbox.com/sh/inwriaq0ttocwzu/AACSB1-ox5z6Z5rm2MaxsYpva?dl=0

          • Sumit Bansal says:

            I think I got the issue.. Replace the function ROW with ROWS, and it should work for you. Hope this helps.

          • torkunc says:

            omg!
            how dumb! I am…
            Thnx a lot and sorry for wasting your time!

          • Sumit Bansal says:

            Glad it worked 🙂

          • Adam Kopeć says:

            I need those macros in Polish, and find this way:

            In VBA I write and run simple macro like:

            Sub Makro1()
            ActiveWorkbook.Names.Add Name:=”Test”, RefersToR1C1:=”=FILES(Sheet1!R2C1)”
            End Sub

            Then in Name Menager I have 4.0 Macro Function name in my Excel language (for my it’s Polish)

  • >