Forum Discussion
Creating a default value from drop down list
I am probably not using the correct terms, but essentially I am trying to have a dropdown list in a cell. I know how to do this. I want it to display the first choice in the dropdown list until the user selects a different one. Essentially it's a default response that is populated in the cell until user selects to change it from the dropdown. Thanks.
You could have the validated cell point at the first cell in the list. Suppose your list populating the dropdown starts at A6, you could enter:
=$A$6....in each cell that requires validation. Example attached.
16 Replies
You could have the validated cell point at the first cell in the list. Suppose your list populating the dropdown starts at A6, you could enter:
=$A$6....in each cell that requires validation. Example attached.
jefort You'll need a small piece of VBA in the worksheet code for that. Now, I'm not a VBA expert, so I just picked-up an example from the web https://www.automateexcel.com/vba/worksheet-change-event/ and adapted it to your schedule.
I have also added some formulae (in columns P and Q) that will work if you are on a modern Excel version that supports the use of UNIQUE and FILTER. Two functions that will be much easier to write, understand and maintain than what you currently have in column M.
Now, if you prefer not to download and open a macro enabled workbook, follow the instructions in the above-mentioned link and paste the following code in the code area for Sheet1.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$G$7" Then Range("G8").Formula = "=M7" End If End Sub
- If changes are made in the validated cell, the formula will be lost. What if changes are made in the source list and I want that the first value from the list would still be the default value in the validated cell?
Riny_van_Eekelen Thank you. Simple and effective. One more question you may know. How do I get this to persist as I add rows with new customers?
Not sure I follow. You mean that you want to expand the range that populates the drop down?
