How can i use these list for multiple rows?

I would like to use these lists for multiple rows and let people enter detailed records.

In my example workbook, I have created three sheets:

• Multiple rows
• Data
• Calculation

The selected drop down list gets all values from a single column on sheet "Calculation".

The array formula on sheet "Calculation" has to "know" which cell you have selected and the adjacent cell value on sheet "Multiple rows".

### Setup an automatic event on sheet "Multiple rows" (VBA)

1. Press Alt-F11 to open VB editor
2. Double click Sheet1 "Multiple rows" in project window.
3. Copy and paste vba code below into code window.
```Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheets("Calculation").Range("D1") = Sheets("Multiple rows").Range("A" & ActiveCell.Row)
End Sub```

Cell D1 on sheet "Calculation" is updated instantly whenever you select a cell on sheet "Multiple rows"

### Setup "Calculation" sheet

Array formula in cell B2:

=INDEX(product, MATCH(0, COUNTIF(\$B\$1:B1, product)+(order<>\$D\$1), 0)) + CTRL + SHIFT + ENTER.

Copy cell B2 and paste it down as far as needed.

unique-distinct-dependent-lists_multiple_rows.xls
(Excel 97-2003 Workbook *.xls)

Recommended posts:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition