How Excel makes a drop-down list

Small series has already introduced a lot of Excel features and properties, today to continue to share is how to use Excel to make a drop-down list, the method is more than one, is not very curious about it? Now to share the 3 ways in which Excel makes a drop-down list.

Excel makes a drop-down list with a map

Making a Drop-down list in Excel can be made by using the data validation, the form control, and the combo box in the VBA Control Toolbox. Let's use a concrete example to illustrate (download the example file in XLS format at the end of the article). If we have a payroll every month, each employee's salary varies according to the number of days of attendance per month.

We need to make a "personal payroll" to see how each person pays each month, and then you can make a drop-down list of employee names in which to view the monthly salary of the assigned employee.

Method one: Using data validation

By using data validation to provide a drop-down arrow in a cell, clicking the drop-down arrow pops up the Drop-down list. Because the employee names are in the monthly payroll and the person's name is not in the personal payroll, we must first define the name to reference when setting the data validation in personal payroll.

The way to define a name is by clicking the menu insert → name, defined in the Definition Name dialog box, where the name area B3:b14 in the January payroll table is defined as "name," as shown in the figure.

1. If the Drop-down list is placed in the C1 cell of the personal payroll, select cell C1, click data → effectivity on the menu, select the Settings tab, and select sequence in the Drop-down list under allow in the validation criteria area.

2. Enter "= name" in the text box under "source". Click OK.

3. The payroll data is associated with the VLOOKUP function in the data area c3:h14. For example, the 3rd Act of January Wages, you can enter a formula in the C3 cell

=vlookup ($C, ' January salary '! $B $: $H $14,2,0) "

Enter formulas in cell D3:

=vlookup ($C, ' January salary '! $B $: $H $14,3,0)

Enter formulas in cell C4:

=vlookup ($C, ' February salary '! $B $: $H $14,2,0)

The rest of the cells, and so forth. This allows you to view the payroll for all months of the month by selecting the employee name by clicking the Drop-down arrow to the right of the C1 cell.

Method Two: Use form controls

1. Right-click on the menu bar, select Form from the pop-up menu, pop-up the Form floating toolbar, click the combo box control.

2. Place the cursor in the table, where the cursor becomes a fine black cross, and a combo box is drawn in the table.

3. Right-click the combo box and select "Format Control" from the menu that pops up.

In the Format Control dialog box, select the Control tab and set the data source area to ' January salary '! $B $: $B $14 "(or" name "), set cell link to $j$1, as shown in the figure.

Click OK to go back to the table, press ESC, or click in any cell to cancel the edit state of the combo box. After this setting, after we select a name in the Drop-down list, the relative position of the name in the list of names appears in cell J1. For example, we select the 3rd name "Wangxia", and the J1 cell returns a value of 3.

4. Use the VLOOKUP function to correlate the payroll data in the data area c3:h14, and use the ordinal number in the payroll to return the payroll data, as in the same way.

Method Three: Use the combo box control in the VBA Control Toolbox.

1. Right-click on the menu bar, select Control Toolbox from the menu that pops up, and pop-up the Control Toolbox floating toolbar, click the combo box control.

2. Place the cursor in the table, where the cursor becomes a fine black cross, and a combo box is drawn in the table.

3. Right-click the combo box and select "Properties" from the pop-up menu.

4. In the Properties window, set the ListFillRange property to ' January salary '! B3:b14 ". Close the Properties window.

5. Press ALT+F11 to open the VBA editor and add the following code to your personal payroll:

Private Sub Combobox1_change ()

Range ("c1") = ComboBox1.Value

End Sub

6. In the data area c3:h14 uses the VLOOKUP function to relate to the salary data, the way ditto.

Alternatively, we can use VBA code to add the employee list to the combo box, and there are two ways to choose:

In the Project window of the VBA editor, double-click ThisWorkBook, and in the Code window on the right, enter the following code:

1. Add items using the AddItem approach:

Private Sub Workbook_Open ()

Dim VName as Variant

Dim I as Integer

' Create a list

VName = Array ("Zhang Mei", "Yellow", "Wangxia", "Army", "Zheng", "Liu Meipo", "Plum", "Wu Yan")

' Use the additem path for i = LBound (vname) to UBound (VName)

Sheet3.ComboBox1.AddItem VName (i)

Next I

End Sub

2. Add items using the List property:

Private Sub Workbook_Open ()

Dim VName as Variant

Dim I as Integer

' Create a list

VName = Array ("Zhang Mei", "Yellow", "Wangxia", "Army", "Zheng", "Liu Meipo", "Plum", "Wu Yan")

' Use the List property

Sheet3.ComboBox1.List = Worksheetfunction.transpose (vname)

End Sub

Where Sheet3 is the worksheet in the Project window of the VBA editor that corresponds to the personal payroll name, which refers directly to the SHEET3 instead of the worksheet name. This way, each time you open the workbook, the list of names is automatically added to the combo box. More wonderful articles please pay attention to download the home Information Tutorial channel.