Excel to set data validation 3 ways to implement a cell Drop-down menu
First, direct input:
1. Select the cells you want to set, such as A1 cells;
2. Select the "data" → "effectivity" → "Data Validation" in the menu bar pop-up window;
3. In the settings option → "Validation conditions" → "allow" → "ignore null" and "Provide drop-down menu" All tick → to the right, enter data under "source", such as "1,2,3,4,5,6,7,8,9" (excluding double quotes, Split symbol "," must be half-width mode) → Click OK to OK, select the A1 cell again, the Drop-down menu appears.
Two, referencing data on the same worksheet:
If a column of the same worksheet is the data that the Drop-down menu wants, such as the b2:b5,b2:b5 that refers to worksheet Sheet1, the following data are available: 1, 2, 3, 4, as follows:
1. Select the cells you want to set, such as A1 cells;
2. Select the "data" → "effectivity" of the menu bar to appear in the Data Validation pop-up window;
3. In the settings option → in the "effectivity condition" → "Allow", select "sequence" → "ignore null value" on the right and " Provide Pull-down menu "All tick →" Enter data under "source" = $B $: $B $ ", you can also press the right side with red arrow directly select B2:b5 area → Press" OK "on OK, select the A1 cell again, the Drop-down menu appears.
( tutorial)
Three, referencing data from different worksheets (you must use the definition name):
If a column of a different worksheet is the data that the Drop-down menu wants, such as the A1 cell for worksheet Sheet1 to refer to the B2:b5 area of the worksheet Sheet2, The b2:b5 of worksheet Sheet2 has the following data: 1, 2, 3, 4, the operation is as follows:
1. Define Name: Menu bar → "Insert" → "name" → "Definition" → pop-up "define name" window, enter "DW" under "Name in Current Workbook" (Can oneself casually clear) → "Reference location" below enter "=sheet2! $B $: $B $", you can also press the right side with Red Arrows directly select B2:b5 area → Press "add" and then press "OK" to complete the first step.
2. Select the "data" → "effectivity" of the menu bar to appear in the Data Validation pop-up window;
3. In the settings option → "validity condition" → "allow" → "ignore null value" and "Provide Pull-down menu" to the right. All tick →Enter "=DW" under "source", "DW" is just defined name, press "OK" OK, select the A1 cell again, the Drop-down menu appears.