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

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

Rick NorrisAsked: 2013-01-25

Who is Participating?

LVL 43

Saqib Husain, SyedEngineerCommented: 2013-01-25

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

LVL 43

Saqib Husain, SyedEngineerCommented: 2013-01-25

This will work if there are no gaps in the data

and

the heights of all columns are the same

Rick NorrisAuthor Commented: 2013-01-26

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