Create dynamic charts with development tools

For example: The premise is to set up a good base table, and later write an article to tell how to build the perfect base table.

first, the simplest form of menu :

There are three factories in a, B and C, which require a three-day production trend chart to be seen through the factory.

Note: The construction of the base table is very important, the date is strictly arranged according to the column.

1, through the Excel development tool-Insert-list box-check the list box right-click to set the control format,

When the A1 is selected, the data of the cell link is displayed as 1, and when the second is selected, 2 is displayed.

2, through the Excel development tool-Insert-scroll bar-check scroll bar right-click to set control format-control, cell link A2, by clicking on the scroll bar to render the production of the different date factory.

3. Remove the selected plant by using the index function combined with the data in the above cell link

Index (the cell that contains the B-C option, the cell link)

4, by the index function combined with the ScrollBar cell link data, the date is taken out. Data can be displayed for several days at a time by setting the properties of the scroll bar.

Index (all dates, scroll bar cell links)

5, through the VLOOKUP function combined with match function matching date, take out the selected factory in a day of production data, can be combined with the Iferror function.

Final reach: You can see data for different dates with the scroll bar selected

二、二级 Menu Making

Example: a factory produces a, B, C three products, B factory production of A, D, E, F four products, C factory production B, F, G, H four products, to see a factory of different products production situation

1, through the PivotTable report, to generate the list box to select the sorting,

Class One: Factory: A, B, C

Level Two: Factory: A, B, C

Factory: B: A, D, E, F

Factory: B, F, G, H

2. Insert two list boxes and a scroll bar to set the control properties respectively. The first-level menu operates correctly based on perspective data.

3, in order to make the first list box selected in a factory, the corresponding product in the second list box, you need to use the name Manager to define the name

CTRL+F3: Opens the name Manager, creates a new name, defines the number of rows that need to be offset using the offset offset function combined with the match function, the number of columns to enter the offset, and the COUNTIF function to intercept the required thickness (a few lines).

The data source range for the 4, Level two menu list box is the newly defined name

The 5, two level menu is selected to be represented by the IF function combined with the index function. ( that is, tell Excel what you have selected )

6. The time setting needs to be combined with the index function to position the data in the cell with the scroll bar.

7, finally through the VLOOKUP function to the selected first and two level option name, base table area, match match time, 0.

Create dynamic charts with development tools