Dynamic list and vertical search in a dynamic area (1).

The Excel tip clip 046 discusses how a dynamic list (dropdown) can be created.
In the Excel 2007 Course, Lesson 37,Vertical search is discussed.

In this tip, we discuss:
1. The dynamic list.
2. Vertical search in a dynamic area.
What is a dynamic list? By a dynamic list, we mean that when add or remove data in a list (column or row), this change is automatically reflected in the list box.

The dynamic list box.

Consider the data from cells A1 through B10 in the image below.

We will create a dynamic list of the data in column A.
To do this, we make use of a formula with the OFFSET and COUNTA functions.

The OFFSET function uses 5 parameters viz, reference, rows, columns, [height] [width].
A reference is required. The relative is a reference to the cell, on which you want to base the offset. The reference must be a reference to a cell or range of adjacent cells. If this is not the case, OFFSET returns # VALUE! as a result.

Rows are required. The number of rows, either up or down, to which the cell in the upper-left corner, refers to, should be specified. For instance, if you enter 5, in the cell in the upper-left corner, it refers to five rows below the current reference. The number of rows can be positive ( a number below the starting reference) or negative ( a number above the starting reference).

Columns are required. The number of columns, either left or right, to which the cell in the upper-left corner, refers to, should be specified. For instance, if you enter 5, in the cell in the upper-left corner, it refers to five columns to the right of the current reference. The number of columns can be positive (a number to the right of the starting reference) or negative ( a number to the left of the starting reference).

The height is optional. The height is expressed as the number of rows that you want to assign to the resulting reference. It must be a positive integer.

The width is also optional. The width is expressed as the number of columns you want to assign to the resulting reference. It should also be a positive integer.

The COUNTA function (number of arguments) counts the number of nonblank cells in a range.

We compose our formula in any cell, so that we can get the formula input help (we do not have the formula help available when we compose the formula in the “New Name” dialog box, which is discussed in tip 12 ).

We type = OFFSET
The first parameter, Reference, (1) is the first cell of the dynamic list.

We click in cell A2, press the function key F4 (to make the cell reference absolute) and type a comma. The formula now is: = OFFSET ($ A $ 2,

For the second parameter, i.e ROWS(2), we need to specify the number of rows relative to the reference we need not shift to and since we don’t have any rows, relative to cell A2, we type a zero followed by a comma.

The formula is: = OFFSET ($ A $ 2, 0,

For the second parameter, i.e COLUMNS(2), we need to specify the number of columns relative to the reference we need not shift to and since we don’t have any columns, relative to cell A2, we type a zero followed by a comma.

The formula is: = OFFSET ($ A $ 2, 0, 0,

For the fourth parameter, HEIGHT(4), we must specify the height of the range of cells to be selected (height refers to how many rows must be included in the selected area)

If we look at the example, the height is 9, i.e, from rows A2 to A10, and we can specify the fourth parameter (height) as 9 but, if there are values entered in the underlying rows, the height is not correct.
We are going to determine the height with the COUNTA function. We then type the last comma.

The COUNTA function:
We must now select a range as a parameter for value1(5).  We select the full range of column A.
We then click on the column heading of Column A, press the function key F4 and type the closing parentheses.

Now the formula is:
= OFFSET ($ A $ 2, 0, 0, COUNTA ($ A: $ A)

In the formula bar, select the COUNTA ($ A: $ A)section in the formula and press the function key F9, and we get 10(6) as the result of this function.

The COUNTA function counts the number of non-empty cells in a range. Since we selected the entire column for the COUNTA function, it also counts cell A1. We, however, find that the number of cells with data are from cell A2 downwards, so that all the cellsthat contain data, which is the first parameter of OFFSET are not allowed to be counted, which is the cell A1, in this case. A1 contains data and references the parameter, so we can say that this value also depends on the COUNTA function.

Press CTRL-Z, to return to the formula = OFFSET ($ A $ 2, 0, 0, COUNTA ($ A: $ A) and place the cursor after the closing parenthesis of the formula.
After the closed parenthesis, we type -1 (Minus 1) and a semicolon.

The last parameter of the OFFSET function, (Width)(7), is the width of the area (width refers to how many columns the selected area should have). Since this parameter is optional, and we do not select additional columns, we can omit it.

Press Backspace once (the semicolon is deleted) and type the closing parenthesis.

The formula is now: = OFFSET ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1)

In the formula bar, select the entire formula, right-click, select Copy and press ENTER. (Note that the formula now gives an error because the formula applied on the selected cells, A2 through A10, can not appear in the cell where the formula is compiled)
Click on the “Formulas” (8) tab in the “Ribbon” and click on “Name Manager” (9), or press CTRL + F3.

We will continue in Part 2

Part 1 – Part 2 –Part 3

Featured Courses

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