Work Summary-Excel application

1. line feed in Excel:
Shortcut: Alt + enter

2. Set the Data Validity in Excel to implement the cell drop-down menu: (3 methods)
1. input directly:
1. Select the cell to be set, such as cell A1;
2. Select "data"> "validity"> "Data Validity" in the menu bar;
3. in the "Settings" option → "validity condition" → "allow", select "sequence" → "ignore null value" on the right and "provide drop-down menu" to check all → "Source" enter the following data, for example, "1, 2, 3, 4, 5, 6, 7, 8, 9" (excluding double quotation marks, the separator "," must be in halfwidth mode) → press "OK, select the A1 cell again, and the drop-down menu is displayed.

2. Reference data in the same Worksheet:
If a column in the same worksheet is the data you want from the drop-down menu, for example, B2: B5 and B2: B5 of Sheet 1 have the following data: 1, 2, 3, 4, the procedure is as follows:
1. Select the cell to be set, such as cell A1;
2. Select "data"> "validity"> "Data Validity" in the menu bar;
3. in the "Settings" option → "validity condition" → "allow", select "sequence" → "ignore null value" on the right and "provide drop-down menu" to check all → "Source" enter "= $ B $2" as follows: $ B $5 ", you can also select B2: B5 area directly by the red arrow on the right → press OK, and then select the A1 cell again, the drop-down menu is displayed.

3. Reference data in different worksheets (the definition name must be used):
If a column in different worksheets is the data you want from the drop-down menu, for example, the A1 cell of worksheet sheet1 must reference the B2: B5 area of worksheet sheet2. The B2: B5 of worksheet sheet2 has the following data: 1, 2, 3, and 4. The operation is as follows:
1. definition name: menu bar → "insert" → "name" → "Definition" → the "definition name" window appears, enter "DW" under "name in current workbook" (you can name it by yourself) → "reference location" and enter "= sheet2! $ B $2: $ B $5 ". You can also press the red arrow on the right to directly select the B2: B5 area → press" add "and then press" OK "to complete the first step.
2. select "data"> "validity"> "Data Validity" in the menu bar.
3. in the "Settings" option → "validity condition" → "allow", select "sequence" → "ignore null value" on the right and "provide drop-down menu" to check all → "Source" enter "= DW" below ", "DW" is the name just defined. Press "OK" and click "A1" again. The drop-down menu is displayed.
note: "= DW" must be entered in the third method!

3. excel Interaction:
there are two worksheets, in which Sheet 1 is used to collect customer data, change the name to "Customer Data Collection" and save all provinces in column A of sheet2, the data in column B is the city and city of each province, and the worksheet name is changed to "Region" (assume that three provinces are set here, namely Jiangsu, Zhejiang, and Anhui ).
. define the name of the cell region
select the cell region B2: B14, click "name" in the "insert" menu, and select "Definition". The "definition name" dialog box is displayed, in the "name in the current workbook" box, enter the name "Jiangsu" and click "OK. Similarly, we can define the names of cells in the counties and cities of Zhejiang Province and Anhui province as "Zhejiang" and "Anhui" respectively ".
B. set the Data Validity
switch to the "User Data Collection" table and select the D column of the province to be selected, click the "validity" command in the "data" menu. The "Data Validity" dialog box is displayed. In the "allow" List of the validity conditions, select "sequence ", in the "Source" box, enter "Jiangsu, Zhejiang, and Anhui" (the separator is a comma in English), and click "OK. Next, select the E column of the "City and city", open the "Data Validity" dialog box again, and select "sequence" in the "allow" List of the Validity condition ", in the "Source" box, enter the formula "= indirect (D2)" and click "OK".
cico prompt: When the dialog box for setting the e-column data validity is closed, the system prompts a warning:" The Source currently contains an error. Do you want to continue? ", You only need to confirm. If you have selected a province in the specified D2 cell and set the Data Validity of column E, this message is not displayed.