Keeping a row of data from being offset

[TitansFan]

Here is what I'm trying to do...

I have two worksheets. One sheet is entitled "Summary" and the other is "D51". When I enter data into cells B2, C2, D2 & E2 on my "Summary" sheet, this info is reflected on my "D51" sheet. When I enter new data into cells B2, C2, D2 & E2 on my "Summary" sheet again, this info is displayed on my "D51" sheet one line, or row, down. In other words, my "Summary" sheet is like a data entry sheet and my "D51" sheet keeps a log, or history of what has been entered on the "Summary" sheet.

Here is the code that makes this happen (thanks to njimack):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then Sheets("D51").Range("A" & Rows.Count).End(xlUp).Offset(1) = Target.Value
If Target.Address = "$C$2" Then Sheets("D51").Range("B" & Rows.Count).End(xlUp).Offset(1) = Target.Value
If Target.Address = "$D$2" Then Sheets("D51").Range("C" & Rows.Count).End(xlUp).Offset(1) = Target.Value
If Target.Address = "$E$2" Then Sheets("D51").Range("D" & Rows.Count).End(xlUp).Offset(1) = Target.Value

Okay, here is the problem that I need help with (it's not easy for me to explain, but here it goes):

Let's say someone enters data into cells B2 and C2 on the "Summary" sheet, but they don't enter data into D2 or E2. This will still create a log on the "D51" sheet. Now, let's say we go back and enter data in B2, C2, D2 and E2 on the "Summary" sheet. The log will again be created on "D51", BUT the row of info is now off because no data was entered previously into D2 and E2.

In other words, I need the info, in a row, to stay together. The user could just enter an "x" or another character into D2 or E2 in the example above, but if someone forgets to do this, it will throw off the log/history.

I guess my goal is to keep this workbook project as dummy proof as I can make it.

I hope I explained it well enough.

Thanks

 

[jasonb75]

Try

If Not Intersect(Target, Range("B2:E2")) Is Nothing Then Sheets("D51").Range("A" & Rows.Count).End(xlUp).Offset(Abs(Target.Column = 2),Target.Column -2) = Target.Value

Note that this single line replaces all of the code in your post.

This works on the basis that users fill from left to right, so B2 will always get filled first.

 

[Marcelo Branco]

Hi,

I think you have to verify the Target.Value for each cell.

Maybe something like

If Target.Address = "$B$2" Then
    Application.EnableEvents = False
    If Target.Value = "" Then Target.Value = "X"
    Application.EnableEvents = True
    Sheets("Plan2").Range("A" & Rows.Count).End(xlUp).Offset(1) = Target.Value
End If

The same for C2, D2, E2

HTH

M.

 

[jasonb75]

As an alternative, how about calling a userform with 4 inputboxes when the user clicks any of the 4 cells, then pass all 4 to the next empty row when the user submits the form.

Should be a little more dummy proof too