A formula that extracts the data to the specified sheet

Today, we share the knowledge of the cell function. Expect to receive a small flower and countless passionate eyes.

In everyday applications, it is often used to split data from the general table. For example, extract the sales data to each sales department sheet, and then extract the student list to each class sheet ...

What we share today is the things that are related to splitting up.

get worksheet names Dynamically

Open an Excel workbook and enter a formula in a cell:

=cell ("filename", A1)

Returns a string of strings, such as D: learn the [ME.XLSX] summary table.

Where "learning" is the name of the folder; "[Me.xlsx]" is the name and type of the workbook; The total table is the name of the worksheet that contains the A1 cell.

If we want to get the name of the worksheet separately, such as the "Total table" here, you can use the text function to process the results of the cell function.

=mid (cell ("filename", A1), FIND ("]", cell ("filename", A1)) +1,99)

The Find function queries the position of the character "]" in the string and adds 1, (why add 1?). You know, the mid function starts to take a number on this result, takes 99 numbers, 99 is a large number, it can also be 66, 88, etc., as long as it exceeds the expected length of the string, change to 250 or 25 is also possible.

Well, this is the first knowledge we've shared today, using the cell function to get the name of a worksheet dynamically.

A small partner said, the name of the worksheet is right there, I can see at a glance, you use the function to calculate it dry see? Do not be too much of a man of the palace scheming ... It certainly does make sense.

Bulk split data

There is such a form, is a company's personnel information table, now according to the gender, the relevant personnel information in bulk into the sub-table, such as girls to fill out the girl, boys fill in the table and so on.

When the information in the main table changes, or there is new data, the data in the table changes accordingly.

Now take a look at the specific steps:

Select a worksheet to split data

Click the Boys worksheet tab on the left, hold down the SHIFT key, and then click the "Shemale" worksheet on the far right, at which point the table outside the total table becomes a "workgroup" with each sub-table selected.

Enter a formula to split the data

In the A2 cell of the grouped worksheet, enter the array formula below, press the key combination <ctrl+shift+enter>, and copy the fill to the A2:b50 area to the right.

=index (General table!) B:b,small (IF (General table!) $C $: $C $13=mid (cell ("filename", A1), FIND ("]", cell ("filename", A1)) +1,99), Row ($2:$13), 4^8), Row (A1 ))) & ""

The sun shines, the Great Wall long--Hey, who, you see, this long old lady binding cloth function comes again, again index+small+if function routine.

I have said before, this is a tiger balm routine, is playing the function of one of the routines, do not know you later learned it? If you don't say anything, I'll do it when you learn it.

Or simply say the meaning of the formula.

MID (cell ("filename", A1), FIND ("]", cell ("filename", A1)) +1,99)

Used to get the table name of the worksheet that contains the A1 cell.

It should be explained that the cell ("filename", A1) the second parameter A1 is not omitted, if omitted, gets will be the last change in the cell's worksheet name, will cause the formula to produce incorrect results.

The IF function determines whether the value of the C2:c13 cell range is equal to the corresponding worksheet table name, and if it is equal, returns the row number of the C column value, otherwise returns 4^8, resulting in an array of memory.

The small function takes the result of the IF function from small to large, and, as the formula is padded downward, extracts the 1th, 2, 3, and 4 in turn. The minimum value of N. This in turn gets the line number of the cell that matches the criteria-gender and the name of the worksheet on which the formula is located.

The index function obtains the result based on the indexed value returned by the small function.

When the small function is given a result of 4^8, which is 65536, it means that the qualifying line number has been exhausted. At this point, the INDEX function returns the value of cell B65536, usually a cell of this size is a blank cell, and A & "" is used to circumvent the problem of returning a value of 0 to a blank cell, so that it returns to false space.

Ungroup Sheet State

When the formula is complete, click the General Tables tab that does not belong to the grouped sheet, and Excel automatically ungroup the sheet state.

At this point, according to the worksheet name of the bulk splitting of the total table data is completed; When the data of the general table changes, the data of the table will change, the ear to listen to the virtual, accreditations, then we see the real chant:

Warm Hearted Tips

You can also achieve the purpose of quickly splitting data by using the PivotTable report Filter page feature or VBA programming. But there are quite a lot of differences between the three in operability, acceptability, dynamic and applicability.

A formula that extracts the data to the specified sheet