Fill data based on drop down month

[IKHAN]

Hi

Have a drop down for month in J5 cell and data is entered based on month in below cells J6:J21 and data calculated in col K using formula.

Require column K data copied over to month (Col L for Jan) and if another month selected in col J5 must add data accordingly to other months columns respectively.

- Require J22 entry to be copied over to that related month too.

- If data available in col L N P ....(months), must be able to retrieve that data in Col J and K based on drop down selected from column J (J5)


See attached sample file

Thanks for assisting...

 

[vletm]

@IKHAN How different? What missed?
http://forum.chandoo.org/threads/ad...-column-from-dropdown-list.33011/#post-195816

 

[IKHAN]

@vletm ..Columns have moved and data to be written in alternate columns.

Please see attached file to initial thread...


Require column K data copied over to month (Col L for Jan) and if another month selected in col J5 must add data accordingly to other months columns respectively.

- Require J22 entry to be copied over to that related month too.

- If data available in col L N P ....(months), must be able to retrieve that data in Col J and K based on drop down selected from column J (J5)

 

[vletm]

@IKHAN ... just different layout ... and You didn't edit ready code?
Okay ...
something like this
... with same instructions as last time

 

[IKHAN]

@vletm ..Thanks . Tried to modify code on attached sample sheet.

Not sure..what field is being missed.

Private Sub Worksheet_Change(ByVal Target As Range)
    ay = Target.Row
    ax = Target.Column
    If ax = 10 Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        With Sheets("Output")
            Select Case ay
                Case 11
                    x = WorksheetFunction.Match(Sheets("Output").Range("J5"), Sheets("Dropdown").Range("G1:G14"), 0) - 3
                    .Range(.Cells(6, 6 + x * 2), .Cells(21, 5 + x * 2)).Copy Destination:=.Range("J6")
                Case 12 To 34
                    yy = WorksheetFunction.Sum(.Range("J6:J21"))
                    .Range("K4:K21").NumberFormat = "0%"
                    For y = 12 To 34
                        .Cells(y, 12) = .Cells(y, 11) / yy
                    Next y
                    x = WorksheetFunction.Match(Sheets("Output").Range("J5"), Sheets("Dropdown").Range("G1:G14"), 0) - 3
                    .Cells(5, 6 + x * 2) = Sheets("Dropdown").Cells(2 + x, 2)
                    .Range("J6:K21").Copy Destination:=.Cells(6, 6 + x * 2)
            End Select
        End With
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

 

[vletm]

1) it is wrong format for 'my way'
2) case ay positions like ay 11 => 5 and 12 to 34 => 6 and 21
3) Your dropdown is 7th column not 2nd
4) Your ranges are '6' too small => '12'
You could find all of those like make it show Ranges then run the code.
Or something like this...

Private Sub Worksheet_Change(ByVal Target As Range)
    ay = Target.Row
    ax = Target.Column
    If ax = 10 Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        With Sheets("Output")
            Select Case ay
                Case 5
                    x = WorksheetFunction.Match(Sheets("Output").Range("J5"), Sheets("Dropdown").Range("G1:G13"), 0) - 2
                    .Range(.Cells(6, 12 + x * 2), .Cells(21, 13 + x * 2)).Copy Destination:=.Range("J6")
                Case 6 To 21
                    yy = WorksheetFunction.Sum(.Range("J6:J21"))
                    .Range("K4:K21").NumberFormat = "0%"
                    For y = 6 To 21
                        .Cells(y, 11) = .Cells(y, 10) / yy
                    Next y
                    x = WorksheetFunction.Match(Sheets("Output").Range("J5"), Sheets("Dropdown").Range("G1:G13"), 0) - 2
                    .Cells(5, 12 + x * 2) = Sheets("Dropdown").Cells(2 + x, 7)
                    .Range("J6:K21").Copy Destination:=.Cells(6, 12 + x * 2)
            End Select
        End With
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

 

[IKHAN]

@vetlm...


Based on dropdown month - data entered in column J and data is changed in column K (on a formula) - need to ONLY copy data over from J and K to respective months/column

J K% data copy over to L M% FOR JAN and so on.

 

[vletm]

...NAHKI
There are NO formulas!
If ONLY numbers then
1) .Range(.Cells(6, 6 + x * 2), .Cells(21, 5 + x * 2)).Copy =>
.Range(.Cells(6, 5 + x * 2), .Cells(21, 5 + x * 2)).Copy
2) .Range("J6:K21"). => .Range("J6:J21).
Couldn't You check those ranges Yourself?
= You would know what to copy ...

 

[IKHAN]

Thanks for your help ..That's not the requirement

Maybe am not able to explain properly

- Sample file attached for ref.

Have a drop down for month in J5 cell and data is entered based on month in below cells J6:J21 and data is calculated in col K using formula(=J6/J$22).

Require ONLY column K data copied(pastespecial) over to month (Col L for Jan) and if another month selected in col J5 must add data accordingly to other months columns respectively.

- Require J22 entry to be copied over to that related month too.

example : L22 , N22 ... (Data is changed manually every month by staff)

- If data available in col L N P ....(months), must be able to retrieve that data in Col K based on drop down selected from column J (J5)


See attached sample file

Thanks for assisting...