Excel table merge methods for multiple tables with the same formatting

Source: Internet
Author: User
Tags one table relative

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.