Solved

Validation Lists Default value

Posted on 2013-05-10
8
283 Views
Last Modified: 2013-05-10
Morning all,

I have two validation list.   The second one is depended on the value in the first.  I was wondering if there was a way after the value in the first list is select that it would default the second list to the first item in the list and you could still change the value if needed.  

Thanks,
Montrof
0
Comment
Question by:montrof
  • 5
  • 3
8 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39155449
You would have to use code for that. We'd need to see a workbook to provide details, really.
0
 
LVL 1

Author Comment

by:montrof
ID: 39155645
0
 
LVL 1

Author Comment

by:montrof
ID: 39155649
Attached is an example of the data.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 39155858
You need to sort your list by region for your DV to work properly by the way (you still get duplicates).

Anyway, right-click the worksheet tab, choose View Code and then paste this in:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D2")) Is Nothing Then
        On Error Resume Next
        Application.EnableEvents = False
        Range("D3").Value = Evaluate(Range("D3").Validation.Formula1)(1)
        Application.EnableEvents = True
    End If
End Sub

Open in new window

0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 1

Author Comment

by:montrof
ID: 39155941
One thing I already have a Worksheet_Change event can you have multiple for one sheet?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39155983
No, you'll have to combine them.
0
 
LVL 1

Author Comment

by:montrof
ID: 39155991
This is the other even i have


Private Sub Worksheet_Change(ByVal Target As Range) more than 13 months
If Intersect(Target, Range("e2")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
    If (Worksheets("List").Range("e6").Value - Worksheets("List").Range("e7").Value) > 396 Then
        Worksheets("List").Range("e3") = Worksheets("Parameters").Range("e161")
    End If
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:montrof
ID: 39155995
could you show me how to combine them I posted the other event

Thanks,
montrof
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now