PDA

View Full Version : Drop Down List to Copy Contents of Another Column into a Certain Column



trpkob
09-26-2012, 05:33 AM
I am looking to implement a drop down menu in which you select A (column H), B (column I), or C (column J) and it fills the values listed in each corresponding column (from the selection) into column F. Furthermore, if the column selected as no value, then it would retain what is already listed in column F. So in the screen shot, if A (column H) were selected from the list, then 03, 04 would be retained as this is blank in the column but 00 would be copied over. So we would have 04, 04, 00, 06, 07, 08, 05, 06, 07,.. in column F. Any suggestions on implantation?

Kenneth Hobs
09-26-2012, 05:50 PM
Did you want to copy the cell with includes formatting, or did you want to just insert the value?

If you would post an example workbook, it is easier to help you.

trpkob
09-27-2012, 05:21 AM
I would just want to insert the value and retain the value for certain cells. In the workbook I have posted H7, H11, H12, H13,.. the cells in the H column which have values would retain those. Then depending on the selection of A, B, or C (Column J, K, & L) from a drop down window, the contents would be inserted for the other cells. So if A was selected then J7 would be inserted into H7, J11 would be inserted to H11, ....

Kenneth Hobs
09-27-2012, 07:42 AM
1. Select J4:L4 and Name it myList.
2. Select H4 and make the Data > Data Validation > Data Validataion > List, =myList.
3. Right click that sheet's tab > View Code, and paste:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range

Set r = Intersect(Target, Range("H4"))
If r Is Nothing Then Exit Sub

Set r = Range("myList").Find(r.Value2)
If r Is Nothing Then Exit Sub

Set r = Range(r.Offset(1), Cells(Rows.Count, r.Column).End(xlUp))
Application.EnableEvents = False
For Each c In r
If IsEmpty(Range("H" & c.Row)) Then Range("H" & c.Row).Value = c.Value
Next c
Application.EnableEvents = True
End Sub

trpkob
09-27-2012, 08:07 AM
I was able to figure it out and get it working using data validation and formulas, thanks.