Assume an MS Excel file has 4 worksheets – Sheet1, Sheet2, Sheet3 and Sheet4. Insert a sheet before Sheet1 and name that tab as Summary. On the Summary tab, one may want to generate a list of all sheet names from cell C7 onwards. Furthermore, the sheet names so generated, should be dynamic for the following changes:
1. Sheets added
2. Sheets deleted
3. Sheets renamed
4. Sheets repositioned
While this can be accomplished by using VBA, you may refer to my formula based solution here.
To generate a list of all Excel files in a specific folder, you may refer to the following post.