Making the dynamic header in Excel with the magical formula

I use Excel to manage a wide variety of forms, which are basically composed of the name of the unit plus the year, month, and table name. When you print these tables every once in a while, you make changes to parts of all the table headers, such as the unit name, year, or month, which are cumbersome and error-prone.

If you can do a dynamic header, all the headings need to change the part of the cell to make a reference, as long as the change in one place, all the headings will be changed together, so much more convenient. Later, after trying to achieve this goal:

1. Create a table header initialization interface

For ease of operation, we first insert a new worksheet, named initialization. In cell C7, enter the text prompt for the "Enter unit name:" So that the D7 cell can be used to enter the unit name.

Next right-click the toolbar, select form, and open the Forms toolbar. Enter "time:" in cell C9, D9 cell to display the year in the header. Click the (Spinner) button on the Form toolbar to draw a spinner object immediately after the cell D9. Similarly, draw a spinner immediately after the F9 cell to adjust the month in the table header.

Enter the year and month characters in the E9 and G9 cells, and align them to the right.

Right-click Spinner 1 and select Set control format to open the Object Format dialog box, and switch to the Control tab in it. In the Current Value box, enter a default year such as 2005, and in the minimum and Maximum boxes, enter the upper and lower limits for the year. Step is 1, and in the Cell link box, enter the absolute address of the year cell to display, such as $D $, when the setting is complete, click the (OK) button, This allows the spinner 1 and D9 cells to be linked well. Similarly, you can link the spinner 2 and the F9 cell, noting that the maximum and minimum values are limited by the month itself.

2. Set the table title

Open the Overtime record worksheet, and enter the formula at the table header position = Initialize! d7&& initialization! d9&& of "Year" && initialization! f9&& "Month" && "overtime Record", press ENTER (you can't see the final effect at this point), and format the title well. Similarly, the title one by one of the other table can be made as long as a slight change is made.

3. Debug Table title

We will be able to debug after the above operation is completed. Go back to the initialization table, enter the school name and select a certain month, and then return to the other worksheet and we'll see the full table heading.

It should be noted that the date function also allows the title to change automatically according to the system time, but it is inconvenient to adjust to a previous year or month.