Excel select from list default value

To use an offset from columns C to Z in the current row:

• select any cell in the first row

• create a named range [Formulas > Name Manager > New...] with Name: e.g. validation and Refers To: would be your formula:

  =OFFSET[$C1;0;0;1;COUNTA[$C1:$Z1]]
  • english-locale users, use , instead of ; as the list separator

• select cells and apply Data Validation > Allow: List, Source: =validation

When you select a cell in 2nd row and observe the Name Manager, you will notice that the formula is using relative references to the current row.

To populate cells with the default value, you can use following formula [as a normal formula inside a cell, it does not have anything to do with the data validation feature whatsoever]:

=INDEX[validation, 1]

and when you actually select a value from the drop-down list, the formula will be overwritten by the selected value, so when you change the 1st item in your list, the value will not change for explicitly selected cells.

This is what I end up with.

Sub DropDownListToDefault[] Dim oCell As Range For Each oCell In ActiveSheet.UsedRange.Cells If HasValidation[oCell] Then oCell.Value = "'- Choose from the list -" End If Next End Sub Function HasValidation[cell As Range] As Boolean Dim t: t = Null On Error Resume Next t = cell.Validation.Type On Error GoTo 0 HasValidation = Not IsNull[t] End Function

The function HasValidation is stolen from here.

After running the macro DropDownListToDefault you will have the following choice after clicking in a DV cell:

Note that in the drop-down list there is no item like - Select from the list - If you want a user to choose something from the drop-down list simply do not accept the default value in further processing.

Video liên quan