Author: Oscar Cronquist Article last updated on February 25, 2018

Sharmila asks:

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.

Download example workbook

Download excel sample file for this tutorial. 
(Excel 97-2003 Workbook *.xls)

Recommended posts:

Functions in this article:

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