Microsoft Office Tutorials and References
In Depth Information
Tip 8: Generating a List of Filenames
Figure 8-2 shows an example. The path and file specification is in cell A1. Cell A2 contains this
formula, copied down the column:
The ROW function, as used here, generates a series of consecutive integers: 1, 2, 3, and so on. These
integers are used as the second argument for the INDEX function. Note that cell A21 (and cells below
it) displays an error. That’s because the directory has only 19 files, and the formula is attempting to
display files that don’t exist.
When you change the directory or filespec in cell A1, the formulas update to display the new filenames.
Figure 8-2: Using an XLM macro in a named formula can generate a list of filenames in a worksheet.
If you use this technique, you must save the workbook as a macro-enabled file (with an
*.xlsm or *.xls extension).