nasplayer.blogg.se - How to add drop down in excel

#HOW TO ADD DROP DOWN IN EXCEL HOW TO#
#HOW TO ADD DROP DOWN IN EXCEL CODE#

For the selected values to be displayed in the same cell separated by any punctuation mark, apply this module.

End(xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub Private Sub Worksheet_Change( ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range( "H2:K2")) Is Nothing And = 1 Then Application.EnableEvents = False If Len(Target.Offset(1, 0)) = 0 Then Target.Offset(1, 0) = Target Else Target.

#HOW TO ADD DROP DOWN IN EXCEL CODE#

For the selected values to be shown from below, insert another code for processing.End(xlToRight).Offset(0, 1) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub Private Sub Worksheet_Change( ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range( "E2:E9")) Is Nothing And = 1 Then Application.EnableEvents = False If Len(Target.Offset(0, 1)) = 0 Then Target.Offset(0, 1) = Target Else Target. With its help, the selected values will be added to the right of the drop-down menu.

#HOW TO ADD DROP DOWN IN EXCEL HOW TO#

The way how to do this is described above. Add a ready-made macro to the sheet module.

Create a standard ComboBox using the «Data Validation» tool.Let's consider the ways of performing this task. Sometimes, you need to select several items from the drop-down list. Selecting multiple values from a drop-down list in Excel Enter the following function: =INDIRECT(А1) in the «Source:» field. If the «Trees», then «Linden», «Maple», etc. It should reflect those words that correspond to the name chosen in the first scroll. Having placed the cursor on the «Source:» field, go to the sheet and select the required cells alternately.Create the first drop-down list, which will include the names of the ranges.Remember that the name cannot contain spaces or punctuation. Above you can see how to turn a normal scroll in a named range (using the «Name Manager»). If the book with the desired values is stored in a different folder, you need to specify the path completely. The name of the file from which the information for the list is taken is enclosed in square brackets. In the «Source:» field, enter the following formula: Activate the cell where we want to put the drop-down menu.

You can solve the problem with the help of the =INDIRECT() function: it will form the correct link to an external source of information. When the values for the drop-down list are located on another sheet or in another workbook, the standard method does not work. Excel drop-down list with data from another sheet / file When you enter a new name in the empty cell of the drop-down list, the following message will appear: «Add entered name Baobab?».Ĭlick «OK» and one more row with the «Baobab» value will be added.

Save it, setting the «Excel Macro-Enabled Workbook» file type.

Private Sub Worksheet_Change( ByVal Target As Range) Dim lReply As Long If > 1 Then Exit Sub If Target.Address = "$C$2" Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Range( "trees"), Target) = 0 Then lReply = MsgBox( "Add entered name " & _ Target & " in the drop-down list?", vbYesNo + vbQuestion) If lReply = vbYes Then Range( "trees").Cells(Range( "trees").Rows.Count + 1, 1) = Target End If End If End If End Sub Copy the code (just insert your parameters). Alternatively, press Alt + F11 simultaneously.

To do this, right-click on the name of the sheet and go to the «View Code» tab. If you do not do this, Excel will not allow you to enter new values.

Clear the following check boxes: «Error Alert», «Show error alert invalid data entered».

Enter a unique name for the range and press OK. Path: «FORMULAS» - «Define Name» - «New Name». Now let's make it possible to enter new values directly into the cell with this list and have data automatically added to the range. The "smart table", which easily "expands" and changes, has helped us to perform our task. Here is our table with a list on one sheet: In the «Source:» field, write the following function:

Open the parameters of the «Data Validation» tool (the path is described above).

Put the cursor on the cell where the drop-down list will be located.

That is, you need to select a table style with a header row. In our example, the header is cell A1 with the word «Trees». For solving our task, design does not matter. Find the «Format As Table» tool in the main menu.

Highlight the range for the drop-down list.

If changes are made to the available range (data are added or deleted), they are automatically reflected in the drop-down list. It is necessary to make a drop-down list with values from the dynamic range.