Just one formula to get Excel to hit the payroll on the head

Use Excel to make payroll by head, there are two solutions on the Internet, one is to take advantage of word "mail Merge" function, the other is to use VBA to achieve.

Using the mail merge function, although not difficult, but too much data is also very troublesome to use VBA to deal with it is very convenient, but dozens of lines of the program is enough for beginners busy. After a trial, the author found a relatively simple method, only a formula can be printed on the head of the payroll.
Create a new Excel file that holds the original data for the payroll in Sheet1, assuming n columns. The first line is the salary item, starting from the second line is everyone's salary

In the Sheet2 we set up the pay bar. According to the actual situation, the wage bar is composed of three lines, one line corresponds to the salary item, a line corresponds to a person's salary data, and then a blank line is used to facilitate cutting. Such three lines constitute a pay bar. The payroll item is on the line with the line number divided by more than 3 digits, and the line number of lines that divide 3 between lines. The above two lines are not difficult to set, the key is the wage data line, involving the Sheet1 and Sheet2 in the corresponding data, after analysis is not difficult to see the "Sheet1 in the Data row =int ((Sheet2 in the data line +4)/3".
So we enter the formula in the A1 cell of Sheet2 =if (mod (Row (), 3) =0, "", IF (mod (Row (), 3) =1,sheet1! A$1,index (sheet1! $A: $N, INT ((ROW () +4)/3), COLUMN ())). After the confirmation select A1 cell, put the mouse in the lower right corner of the A1 cell, the mouse becomes "+", drag the mouse to the right to automatically fill to n column, so that the first line of the pay bar comes out. Select A1:n1, put the mouse in the lower right corner of the N1 cell, the mouse again into "+", drag down the mouse to automatically fill the last line of data, the payroll on all the production completed.
The formula uses the IF function to judge the reference line number of the MoD function and the remainder of 3. If the remainder is 0, a blank row is generated, and if the remainder is 1, the contents of the first row in the Sheet1 are fixed, or the index function and the INT function are used to fetch the number on the corresponding row of the Sheet1.

Finally to set the format, select A1:n2 set up the table line, blank line is not set. Then select A1:n3, drag the N3 fill handle down to automatically fill, so that there is a table line of data, no data, no table lines. Finally adjust the margins, do not put a pay bar on the two pages. How are you, satisfied?