Choose a single list value – Part 2

We must find a way to place names in the cell range F3 to F22 which have not yet been used to indicate a numeric value.

We do this with the rest of the formula:
IF ($ H $ 3: $ H $ 22 = “”, ROW ($ H $ 3: $ H $ 22)-ROW ($ H $ 3) +1), ROWS ($ I $ 3: I3)
The first part: IF ($ H $ 3: $ H $ 22 = “”, which denotes that If the cells H3 to H22 are empty(Remember, this is an array formula, so the entire range of cells is considered) this is the result:
{TRUE, TRUE, TRUE, TRUE,FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE,
TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE}
The FALSE comes from the name “Marc” in cell H7 in our example.
H3 = Empty cell, H4 = blank, H5= blank, H6 = empty, H7= not empty, H8 = empty, etc.
From the second part: ROW ($ H $ 3: $ H $ 22)-ROW ($ H $ 3) +1) first consider the portion ROW ($ H $ 3: $ H $ 22).
This gives the matrix {3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22}
The function = ROW, returns the row number counting from row 1(From cell H1).
However, we have (F3: F22) in our index, between 1 and 20 (the 20 names).
So, we must obtain a matrix from 1 to 20, which is satisfied.
-ROW ($ H $ 3) +1) gives the row number of cell H3 (= 3) depending on all the numbers in the matrix, which gives {0,1,2,3,4,5,6,7,8,9,10, 11, 12, 13, 14, 15, 16, 17, 18, 19} and then count of all numbers in the matrix is increased by 1, which results in: {1,2,3,4,5,6,7,8,9,10; 11, 12, 13, 14, 15, 16, 17, 18;} 19.20
The result of the If part of the formula: IF ($ H $ 3: $ H $ 22 = “”, ROW ($ H $ 3: $ H $ 22)-ROW ($ H $ 3) +1) gives:

{1, 2, 3, 4;FALSE, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20}
Cell F3 has a numerical value of 1, F4 has a numerical value of 2,
F5 has a numerical value of 3, F6 has a numerical value of 4, F7 does not have any numerical value, F8 has a numerical value of 6, etc.
Now we need to transfer the cells containing a numeric value to I3 till I23.
We do that with the second parameter of = SMALL, and for the cell I3 that is ROWS ($ I $ 3: I3)from I3 to I3 = 1
(The function ROWS returns the Number of rows in a reference.)
When we copy the formula down, for the cell I4 ROWS ($ I $ 3: I4) i.e, from I3 to I4 = 2, etc.

Therefore, the smallest value goes to I3, and the second smallest value goes to I4, etc.
For cell I3, the formula is, therefore:
=IF (ROWS ($ I $ 3: I3)> COUNTA ($ F $ 3: $ F $ 22) -COUNTA ($ C $ 3: $ C $ 22), “”,
INDEX ($ F $ 3: $ F $ 22, SMALL (IF ($ H $ 3: $ H $ 22 = “”, ROW ($ H $ 3: $ H $ 22)-ROW ($ H $ 3) +1), ROWS ($ I $ 3: I3)))).
We now press CTRL + SHIFT + ENTER. The formula will now change to a matrix formula:
{= IF (ROWS ($ I $ 3: I3)> COUNTA ($ F $ 3: $ F $ 22) -COUNTA ($ C $ 3: $ C $ 22); “”;
INDEX ($ F $ 3: $ F $ 22, SMALL (IF ($ H $ 3: $ H $ 22 = “”, ROW ($ H $ 3: $ H $ 22)-ROW ($ H $ 3) +1), ROWS ($ I $ 3: I3))))}
Then copy of the formula down to cell I22.

Now, we have a dynamic list for the cells C3 to C22. We can do that with the OFFSET and COUNTA functions.

The OFFSET function uses 5 parameters viz reference, rows, columns, [height] [width].
Reference is required. The reference is relative to where the shift to going to take place. Reference must be a reference to a cell or a range of adjacent cells. If this is not the case, OFFSET returns the # VALUE! as a result.

Rows is also required. This is the number of rows, either up or down, to which the upper-left cell to refers to. If you give the number, 5, the resulting reference of the upper-left cell is five rows below the reference. Rows can be a positive number (or a number below the starting reference) or a negative number (or a number above the starting reference).

Columns is required. The number of columns, either left or right, to which the upper-left cell to refers to. If you give the number, 5, the resulting reference of the upper-left cell is five columns to the left of the reference. Columns can be a positive number (or a number to the left of the starting reference) or a negative number (or a number to the right of the starting reference).

The height is optional. The value is expressed in terms of the number of rows that you want to assign to the resulting reference. Height must be a positive integer.

The width is optional. The widthis expressed in terms of the number of columns you want to assign to the resulting reference. Width must be a positive integer.

An example: I type the following formula in cell A1 = OFFSET (A7, -4, 3, 2, 2)

This means: go from cell A7, 4 rows up, 3 columns to the right, the height is 2 cells and the width is 2 cells.

The result: a matrix of values ??in th cell area from C3 to D4 = {“B” \ “C”, “F” \ “G”}
The slash \ in the result is the separator for columns and the comma( , )for rows.

Type the formula for the dynamic list, in an empty cell (It doesn’t matter which cell, its just that we can use the auxiliary input formula again). Here’s the formula:
= OFFSET ($ I $ 3, 0, 0, COUNTA ($ F $ 3: $ F $ 22) -COUNTA ($ C $ 3: $ C $ 22))
The first parameter (reference) = I3
The second parameter (rows) = 0
The third parameter (columns) = 0
The fourth parameter (height) can be calculated using the COUNTA function = COUNTA ($ F $ 3: $ F $ 22) -COUNTA ($ C $ 3: $ C $ 22))
The fifth parameter (width) is optional and we do not use it.

Then we select and cut the formula from the formula bar (8), and we place the cursor in another cell.

We go to the “Formulas” (9) tab on the ribbon and click on “Name Manager” (10).

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

In the next dialog box, “New Name” we erase the data in the “Refers to” (12) text box and we stick to the formula, and in the “Name” (13) text box, we type an appropriate name for our list. We then click OK.

FYI: Swotster.com thinks of its students, so you also please remember swotster.com

We continue Part 3

Part 1 – Part 2 – Part 3

Featured Courses

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