This method uses the legacy Excel 4 XLM functions. These functions aren’t like Excel’s other functions such as SUM, VLOOKUP, INDEX etc. These functions won’t work in a regular sheet, they only work in named functions and macro sheets. For this trick, you will need to create a named function. Use the above link to download the example workbook from this post. Create a named function. Specify the folder path. This is an easy way to list all the files in a given folder and no VBA is needed. A word of caution is needed. Since these XLM 4 functions are a legacy feature in Excel, they are not supported and shouldn’t be relied upon for anything critical.
