Excel table merge methods for multiple tables with the same formatting

There are multiple Excel files, and there are more than one table (Sheet) in the file, meaning that different Excel files (Sheet) have the same table format and different values in the table. You want to create a new rollup Excel file that summarizes the corresponding (Sheet) tables in multiple child Excel files in a single file.

Example:

There are 2. xls files named 1.xls and 2.xls respectively

There are 3 different format tables Sheet1, Sheet2, Sheet3 in each file, but the table contents in the same sheet in the XLS file are the same.

I made a formula: summary table b2=[2.xls]sheet1! $B $2+[1.xls]sheet1! $B $

The problem also comes, usual in the mouse pointer into a black cross when the formula with the downward drag method can automatically add the formula corresponding to the table automatically 1, that is, can be in the B3 into =[2.xls]sheet1! $B $3+[1.xls]sheet1! $B $. But it didn't happen.

All you have to do is replace the absolute reference with a relative reference.

b2=[2.xls]sheet1! b2+[1.xls]sheet1! B2

You learn about relative references, absolute references, mixed references, and this kind of problem can easily be solved.