How Excel function formulas combine multiple worksheets

how Excel function formulas combine multiple worksheets

For example, in the workbook shown in the following illustration, A,b,c,d is a table with 4 different rows in the same row, and the 4 tables need to be merged into the totals table.

Operation Steps:

1. Formula-Name Manager-new name-Enter the name "sh" in the new name, and then enter the formula in the Reference Location box:

=mid (get. Workbook (1), Find ("]", get. Workbook (1)) +1,99) &t (now ())

Formula Description:

Get. Workbook (1) is a macro table function, when the argument is 1 o'clock, you can get all the worksheet names in the current workbook, because the name contains the workbook name, so use Find+mid to intercept a string containing only the sheet name. The role of &t (now ()) is to have the formula automatically updated.

2. Enter the following formula in column A:

=index (Sh,int (ROW (A1)-1)/6) +1)

Formula Description:

The purpose of this formula is to automatically populate the name of a sheet in column A and fill the next name with every n row replacement. The maximum number of rows in a formula 6 is the current or future update of each table, as much as possible. To avoid future additions to the row summary table cannot update the data. SH is the name added to step 1th.

3. Enter the formula in B2 and fill it down to the right to get the data of each table.

=indirect ($A 2& "!") &address (COUNTIF ($A $: $A 2, $A 2) +1,column (A1))

Formula Description:

The purpose of this formula is to use the indirect function to get the value of the table based on the table name of column A. Where the address function generates cell addresses based on the number of rows and columns, such as the result of 1,1 is $a$1.

After the formula is set up and copied, you will find that the data for each table has been merged.

When you delete a table, the table data is automatically deleted in the summary table, and the table data is added automatically when you add a new job.