Multiple Worksheets & Workbooks

Input: Multiple Excel files with multiple sheets of data.

Download sample files here

Goal: Setup a DataFromExcel formula that collates the data from all the workbooks and worksheets and puts it in one single place.

Hint:

  1. Get the file list using GetAllFiles.SK
  2. Define a function which will take in a file_name as input and return a two column list of the file_name & the sheet_name. (Use SheetNames.SK)
  3. Use the above defined function on the list of files
  4. Use DataFromExcel.SK, where you can pass in the file_name & sheet_name via RegionDown.SK to get the data in one single place.