Solved

Excel:  Dynamically changing drop down items AND data for the items.

Posted on 2013-01-25
3
398 Views
Last Modified: 2013-01-26
Experts:

I asked a previous question.  Was answered completely...  this is a followup.  You may want to refer to below, and the solution.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28008108.html

Additional question:

I would now like to have the data in columns g thru k also move when the dropdown item list changes.

Please see attached Excel file for example of what I need.  Please see code behind the "data" tab for what is working currently...  Code was very kindly provided by ssaqibh!

thanks in advance.

Rick D Norris
DropDownExample---New.xls
0
Comment
Question by:Rick Norris
  • 2
3 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 38821600
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range
    Dim scel As Range
    Dim dv
    Dim dvn() As String
    Dim nf As Boolean
    Dim tmp As Variant
    If Target.Count = 1 And Not Intersect(Target, Range("G1:K1")) Is Nothing Then
        For Each cel In Range("G1:K1")
            If cel.Address <> Target.Address Then
                If cel = Target Then
                    dvn = Split(Replace(ThisWorkbook.Names(Replace(cel.Validation.Formula1, "=", "")).RefersTo, "=", ""), "!")
                    For Each dv In Sheets(dvn(0)).Range(dvn(1))
                        nf = False
                        For Each scel In Range("G1:K1")
                            If scel = dv Then nf = True: Exit For
                        Next scel
                        If Not nf Then
                            cel = dv
                            tmp = Target.Offset(1).Resize(Target.End(xlDown).Row - Target.Row).Value
                            Target.Offset(1).Resize(Target.End(xlDown).Row - Target.Row).Value = cel.Offset(1).Resize(cel.End(xlDown).Row - cel.Row).Value
                            cel.Offset(1).Resize(cel.End(xlDown).Row - cel.Row).Value = tmp
                            Exit Sub
                        End If
                    Next dv
                End If
            End If
        Next cel
    End If
End Sub
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38821604
This will work if there are no gaps in the data

and

the heights of all columns are the same
0
 

Author Closing Comment

by:Rick Norris
ID: 38822133
ssaqibh:

‘Dwarfs standing on the shoulders of giants see farther ahead than the giants.’ Pascal

I am a dwarf, but with your help, my software project has been enhanced well beyond what I was capable

My example did not take into account the very HIGH probability of "gaps".... concerning heights......  Not sure if I understand???

Because the constraint you mentioned in your solution would not work in what I want as an end product, I am posting another follow-up question.  I have included an example that is a sampling of data that is representative of what I will be importing into my software.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28009826.html

You continued participation is very much appreciated.

Thanks again,
Rick Norris
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
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 demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

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

11 Experts available now in Live!

Get 1:1 Help Now