Input: Multiple Excel files with data starting at different positions in the top 20 rows. The data is known to be in the first few columns but the row where the data starts is uncertain.
Download sample input files here
Goal: Setup a workbook formula that pulls the data from all the files in the given sample set (immaterial of where the data starts) and puts it in one single place.
- Define a function which will return the row where the data starts given a file.
- Use GetAllFiles.SK to get the set of input files.
- Run the function on each file to get the corresponding data start row.
- Use string concatenation in Excel to define the range as "RegionDown.SK(A"&datastartrow&":G"&datastartrow&")"
- Use the range for each file as an input into DataFromExcel.SK to get the data from the respective files.