How do I combine rows from two files in Excel?

Answer 1

Suppose you want the 17th row of FirstFile.xls, worksheet FirstSheet and the 23rd row of SecondFile.xls, SecondSheet, and you want them copied into MyResultFile.xls, the first 20 columns of each.

The key is to use square brackets around the file name, and the exclamation point ( ! ) as the operator between the worksheet name and the cell.

in MyResultFile.xls, into A1 type this formula:

=[FirstFile.xls]FirstSheet!A17

In MyResultFile.xls, into A2 type this formula:

=[SecondFile.xls]SecondSheet!A23

Now, in MyResultFile.xls, select A1 and A2 and copy them into A1:T2. This does the job.

By the way, if a file name has spaces, put single quotes around the name:

=[‘My Spacey File Name.xls’]TheSheet!A17