Dynamic list and vertical search in a dynamic area (2):

In the “Name Manager” dialog box, click on the “New” (10) tab.

In the next dialog box, which is “New Name”, erase the data in the “Refers to:” text box and paste the formula (11). Then in the “Name” text box, enter an appropriate name for the list, eg list (12 ) and click Ok.

We now return to the “Name Manager” dialog box and see that in the “Refers to” box, the formula has adjusted the cell references for the name of the sheet (13) so that, when the dynamic list is inserted on another sheet, the correct cell range is referenced.
Click “Close”.

You can check this by selecting another sheet, for example sheet 2 and then press CTRL + F3 and in the “Name Manager” dialog box, click the “Name” list (14) and then in the “Refers to” text box, click on the selection icon (15).

The “Name Manager” dialog box is minimized, “Sheet1” is selected and the cell area is indicated by blinking dotted lines (16).

Click on the selection icon, again, and click “Close”.
Select “Sheet1” again.

Select the cell containing the formula you have created and press DELETE.

Select the cell in which you want to place the list box (in this example it is cell F1).
Click on the “Data” (17) tab and click on the “Data Validation” (18) button in the “Ribbon”.

In the “Data Validation” dialog box, select the “List” option for the “Allow” text box (19) and click in the “Source” (20) text box.

Press the function key F3 and the “Paste Name” dialog box appears. Click “List” (21) in the “Paste Name” dialog box and then click OK.

In the “Data Validation” dialog box, click on OK.
In this example, I worked with dates in column A which return the numeric value for dates in cell F1, so I change the cell format to date, for eg. “Short Date”.
Our list is ready.

You can test the operation of the list data by adding or deleting data from column A.

We continue in Part 3

Part 1 – Part 2 – Part 3

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats