Required tools: computer, Excel.
1. In the sheel 1 worksheet of Excel, enter the name of the item to be used in column A, select the entire table column of column A, enter the item in the cell of column A in the upper left corner, and then press Enter.
2. Select the number of cells in column A where the project name needs to be entered in the sheet2 worksheet, find and click Data Validity in the upper right corner, and then click Options Data Validity in the menu bar below.
3. Click Data Validity, select the allowed sequence in the pop-up window, enter the formula = Item in the source, and then click OK in the lower right corner.
4. Enter the following formula in the B2 cell of the worksheet:
=IFERROR(VLOOKUP(A2，sheet 1！ A:B, 2, 0), ""), after the input is completed, press Enter, and then drop down to all cells in B2.
5. Click the arrow in the cell in column A and select any item. Take the electronic dictionary as an example.
6. It can be seen that after A3 chooses to enter the electrician dictionary, B3 will display the books accordingly.
Extended data:
The following principles should be followed when designing Excel tables:
1, data management principles
We should have a good concept of data management, define the four steps of data processing, and distinguish three types of tables.
In practical work, firstly, several types of tables need to be used are determined according to the size of data, then the types of tables are determined according to the purpose of the tables, and then the overall structure and layout of all kinds of tables are determined.
The structure of list form depends on the type and complexity of data content and is based on the principle of facilitating data processing; The report is user-oriented, and on the basis of clearly and intuitively reflecting the information, the habits of report users are properly considered.
2, the principle of consistency
The same thing has the same name: that is, an object can only have one name, and the name of the same object should be consistent in any table, person, department or company in the group for data reference.
Same format as table: In order to collect statistics and data, the format of the same table must remain unchanged.
In addition, the names of similar workbooks/worksheets should be in the same format, so that formulas can be modified in batches when tables are renovated.
3. Normative principles
Name specification and format specification: all kinds of data in the table are in standardized format, such as numbers in conventional or numerical format, and date data cannot be in 20 120 106, 2012.16,12.1.
4, the principle of integrity
Worksheets of the same project and type are placed in the same workbook, and workbooks of the same type are placed in the same folder. It is convenient for statistical analysis of data and editing and modification of formulas.
5. Safety principle
Data validity can be used to check input data when inputting data; The table to be distributed should protect the worksheet and only allow other users to modify the cells that can be modified; If you reference data from other workbooks, you should break the link when it is not needed, so as to avoid losing the data in this table after the source table is deleted or moved. In addition, we have formed the habit of backing up data regularly.
6, the principle of scalability
The edited formula should have good expansibility. Table names should be standardized and regular, and cell references should use relative references and absolute references correctly, so that the formula can be filled with the mouse.