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
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)
- Press Alt-F11 to open VB editor
- Double click Sheet1 "Multiple rows" in project window.
- 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:
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)
- Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
- Create dependent drop down lists containing unique distinct values in excel
- Apply dependent combo box selections to a filter in excel 2007
Functions in this article:
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
Returns the relative position of an item in an array that matches a specified value
Counts the number of cells within a range that meet the given condition