Author: Oscar Cronquist Article last updated on January 07, 2019

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.

Answer:

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"

Setting up the "Calculation" sheet

Array formula in cell B2:

=INDEX(product, MATCH(0, COUNTIF($B$1:B1, product)+(order<>$D$1), 0))

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Copy cell B2 and paste it down as far as needed. You can read how this formula works here: 5 easy ways to extract unique distinct values

Download Excel file

Enter your email to receive the workbook.
* You will also get a weekly newsletter, unsubscribe anytime!