1. First of all, a workbook should prepare two worksheets, one is "employee information", which is used to input the information of all employees; The other is "Lookup", which is used to display the information of each employee one by one. ?

2. Enter the information of all employees in the "Employee Information" table, and put the photo of each employee in the fourteenth column (that is, the N column). Photos can be copied and pasted or "Insert-Picture-From File". If the size of the picture is larger than N columns of cells, you can click the picture with the mouse, and when there are 8 small circles around the picture, you can zoom the picture with the mouse. Finally, you must put the picture in the center of each cell in N columns.

Step 2: Enter the formula.

1. In the lookup table, set the items according to the above figure and enter the following formula:?

E2 cell, displaying serial number, formula = offset (employee information! $A$ 1，$F$ 1，0)？

Meaning of the formula: use the OFFSET function to start from the cell A 1 of the employee information table, and shift down by one row ($F$ 1 indicates the value in the cell F 1 of the search worksheet, that is, the number of offset rows), and shift 0 columns to the right in the cell where the formula is located to display the cell contents. ?

General usage of 2.2. Offset function:? OFFSET (starting cell, number of rows offset below, number of columns offset to the right)?

The offset of the starting cell itself is zero, and when the offset is negative, it will move in the opposite direction.

Tip: Since the "Starting Cell" is not in the current worksheet, you should add the label name of the target worksheet, namely "Employee Information! $A$ 1 ",and" offset row "refers to the cell F 1 of the current worksheet, so there is no need to add the label name of the worksheet before F 1.

3.B3 cell, display name, formula = offset (employee information! AUD 1 USD, NLG 1, 1 USD)

Formula meaning: As above, use the OFFSET function to get the corresponding employee name. The last parameter 1 represents a column offset to the right from cell A 1 of the employee information table, that is, column b of the employee information table.

D3 cell, showing ID card, formula = offset (employee information! AUD 1 USD, NLG 1.2 USD)

B4 cell, showing gender, formula = offset (employee information! 65438 Australian dollars +0 US dollars, 65438 Dutch guilders +0.3 US dollars)

D4 =OFFSET (employee information! AUD 1 USD, NLG 1.4 USD)

B5 = Offset (employee information! 65438 Australian dollars +0.5 Canadian dollars)

D5 =OFFSET (employee information! AUD 1 USD, NLG 1.6 USD)

B6 =OFFSET (employee information! 65438 Australian dollars +0.7 Canadian dollars)

D6 =OFFSET (employee information! AUD 1 USD, NLG 1.8 USD)

B7 =OFFSET (employee information! 65438 Australian dollars +0 US dollars, 65438 Dutch guilders +0.9 US dollars)

B8 = Offset (employee information! 65438 Australian dollars +0 US dollars, 65438 Dutch guilders +0.65438 yuan)

B9 = Offset (employee information! AUD 1 USD, NLG 1,1/USD)

D9 = Offset (employee information! AUD 1 USD, NLG1.1USD)

Step 3: Add a button

Add a "value adjustment button" after the formula is completed.

1. Press View-Toolbar-Control Toolbox to bring up the toolbar of Control Toolbox, select "Value Adjustment Button" from it, and then add it under cell F 1.

Tip: This "Value Adjustment Button" is similar to the "Fine-tuning" button in the Form Control, but the property settings are different.

2. Right-click the add adjustment button and select Properties to open the property setting window of the control, as shown in the following figure:

3. LinkedCell in the attribute represents the linked cell, which is set to "F 1" in the figure. When the value adjustment button is pressed, the corresponding value will be displayed in cell F 1.

Max in the attribute indicates the maximum display value, which can be set according to the maximum number of employees. In this example, it is set to "100".

Min in the property represents the minimum display value, which is set to "1".

4. After setting, you can press "Value Adjustment Button" to display employee data one by one. When pressed, when cell F 1 displays 1, the employee information with serial number 1 will be displayed by the formula in the table; When cell F 1 is 2, employee information with serial number 2 will be displayed.

Tip: If the value in the box F 1 does not change when the Value Adjustment button is pressed, it may be that the control is still in the "Design" state, and you need to click "Exit Design Mode" in the toolbar of the control toolbox.

Step 4: Show the photos.

1. Compared with data, it is more troublesome to display photos.

E3: E6 in the search worksheet is a merged cell, so photos cannot be displayed directly. You need to insert a control to display photos.

2. Select the "Text Box" control from the Control Toolbox and add a medium-sized text box control in the E3:E6 area.

3. After adding the text box, select it with the mouse, and you will find a formula in the edit bar at the top of the table, which needs to be modified.

Change the formula to "= picture".

4. The equal sign in the formula should be entered in English, and the "picture" after the equal sign is the defined name. Let's define this name.

Press Insert-Name-Define to open the Define Name dialog box. Enter Picture in the name in current workbook box, and enter the formula =OFFSET (employee information! $A$ 1, come and have a look! $F$ 1, 13) ",just press" OK ".

The formula of this reference position also uses the OFFSET function, which is the same as the usage introduced above.

Step 5: Verify the effect.

1. After the above settings are completed, you will see employee photos appear in the added text box control. With the change of the value of the Value Adjustment Button, you can display the corresponding employee photos.

Employee data with serial number 1

2. Employee data with serial number 2.