MS Excel Formula Magic

General Tips-And-Tricks

Generate a list of files from the OS directory specified in a cell using MS Excel.

Start with a Excel Workbook and follow these steps to create a named formula.

  • From the Formula menu choose Define Name -> Define Name
  • From New Name Dialog box provide formula name, say FilesList. Leave Scope to Workbook.
  • In the Refers to box type: =FILES(Sheet1!$A$1)

The FILES function is an old XLM style macro function which takes one argument, a directory path and a file specification (you can use wildcard) and returns an array of filenames in that directory that match the file specification.

After defining the named formula

  • Enter a directory path and file specification into cell A1 in Sheet1, e.g, d:\*.txt
  • Type this formula in any cell of the worksheet: =INDEX(FileList, 1)

It will simply place the first file matching the filespecs “D:*.txt”

  • Change the last parameter from 1 to 2 or 3 and so on and see the result. It will show the next file names.

This method is magical but not quite helpful. Let’s list down all files matching this specification from the directory.

  • Type =INDEX(FileList,ROW()-1) in any column in the first row. It will display the first file. Nothing much different, right?
  • Now copy this cell to several cells below and now see the real magic. It will display more or all file names in those cells.

Cool. Isnt it?

Generate a list of Worksheets in the current Workbook:

This time create a named formula, say SheetsList and provide this formula:

=REPLACE(GET.WORKBOOK(1),1,FIND(“]”,GET.WORKBOOK(1)),””)

Don’t panic, just assume that it will return the created worksheet names in this workbook. Now to list down the names of those sheets type this:

=INDEX(SheetList,ROW())

and copy this cell down to as many cells as the number of sheets, or you may continue. If you want to avoid error because of exceeding the number of cells then enclose the formula in IFERROR like below:

=IFERROR(INDEX(SheetList,ROW()),””)

Remember you must save the workbook as a macro-enabled file (with an *.xlsm or *.xls extension) if you use this technique.

Leave a Reply

Your email address will not be published. Required fields are marked *