Automating Manual Date Changes recorded on another sheet.

[hakantoz]

I have two sheets on the first sheet, Column A have the project name, I have Columns K, L, U where I have dates that change on a weekly basis. On Sheet two I have same thing starting with A27, I have the same project names, however on F27, G27 and H27 where I manually enter the historical changes of dates manually stacked.

What I am trying to do is on sheet two, if K, L, U dates change at any time on sheet one, record them on sheet two, for the right project(names to match ) and have those stacked where the newest value is on top in the cell and past ones are pushed to bottom.

i.e. where on sheet 1 on U column, once date 3 changed to 3/3/2018 for project A , it should add it on sheet 2, column H, for project A, for the Date 3.


Sheet1
A K L U
[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Project[/TD]
[TD="width: 64"]Date1[/TD]
[TD="width: 64"]Date 2[/TD]
[TD="width: 64"]Date 3[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD="class: xl66, align: right"]1/1/2018[/TD]
[TD="class: xl66, align: right"]1/1/2018[/TD]
[TD="class: xl66, align: right"]3/3/2018[/TD]
[/TR]
[TR]
[TD]Project B[/TD]
[TD="class: xl66, align: right"]2/2/2018[/TD]
[TD="class: xl66, align: right"]2/2/2018[/TD]
[TD="class: xl66, align: right"]2/2/2018[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2
A E F H
[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Project[/TD]
[TD="width: 64"]Date1[/TD]
[TD="width: 64"]Date 2[/TD]
[TD="width: 64"]Date 3[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD="class: xl66, width: 64"]1/1/2018
5/5/2017[/TD]
[TD="class: xl66, width: 64"]1/1/2018
6/6/2017[/TD]
[TD="class: xl66, width: 64"]3/3/2018
1/1/2018
7/7/2017[/TD]
[/TR]
[TR]
[TD]Project B[/TD]
[TD="class: xl66, width: 64"]2/2/2018
3/3/2017[/TD]
[TD="class: xl66, width: 64"]2/2/2018
4/4/2017[/TD]
[TD="class: xl66, width: 64"]2/2/2018
5/5/2017[/TD]
[/TR]
</tbody>[/TABLE]


Also wondering whether a way to utilize concatenate here without vba?

Thank you so much for all help!

 

[mumps]

There seems to be a little confusion as to the column letters in Sheet2. In your description you refer to "F27, G27 and H27" but where you show the data in Sheet2, you refer to "E F H". Please clarify. You say that on Sheet2 the project names start at A27. What do you have in A1:A26? Also, are the headers exactly entered as "Date1", "Date 2", "Date 3" on both sheets and are those headers in row 1 of both sheets?

 

[hakantoz]

My apologies, I also realized I was missing something, Please see the links attached for sheet 1 and sheet2. I have other information present between A1:A26 on sheet 2, so that is why I started from that location. And you are also right that DATE1,2,3 are headers for both sheets.Columns on both sheets should be accurate now. ( Just FYI, I have many projects like this going down in the list on both sheets, 2 projects were given as example layout )

Sheet 1 : https://ibb.co/cQN3be

Sheet 2: https://ibb.co/efmwwe

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a date in a cell in columns K, L, R or U and exit the cell. Columns E, F, G and H in Sheet2 will be automatically updated.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("K:L,R:R,U:U")) Is Nothing Then Exit Sub
    Dim project As Range, rDate As Range
    Set project = Sheets("Sheet2").Range("A27:A" & Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row).Find(Cells(Target.Row, 1).Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not project Is Nothing Then
        Select Case Target.Column
            Case Is = 11
                Set rDate = Sheets("Sheet2").Rows(26).Find(Cells(1, 11).Value, LookIn:=xlValues, lookat:=xlWhole)
                Sheets("Sheet2").Cells(project.Row, rDate.Column) = Target & Chr(10) & Sheets("Sheet2").Cells(project.Row, rDate.Column)
            Case Is = 12
                Set rDate = Sheets("Sheet2").Rows(26).Find(Cells(1, 12).Value, LookIn:=xlValues, lookat:=xlWhole)
                Sheets("Sheet2").Cells(project.Row, rDate.Column) = Target & Chr(10) & Sheets("Sheet2").Cells(project.Row, rDate.Column)
            Case Is = 18
                Set rDate = Sheets("Sheet2").Rows(26).Find(Cells(1, 18).Value, LookIn:=xlValues, lookat:=xlWhole)
                Sheets("Sheet2").Cells(project.Row, rDate.Column) = Target & Chr(10) & Sheets("Sheet2").Cells(project.Row, rDate.Column)
            Case Is = 21
                Set rDate = Sheets("Sheet2").Rows(26).Find(Cells(1, 21).Value, LookIn:=xlValues, lookat:=xlWhole)
                Sheets("Sheet2").Cells(project.Row, rDate.Column) = Target & Chr(10) & Sheets("Sheet2").Cells(project.Row, rDate.Column)
        End Select
    End If
End Sub

 

[hakantoz]

Thanks so much mumps, this code works perfect.

I do have 2 follow up questions;

1) On my sheet 1 , any columns I mentioned above that are following column K actually have formulas. ( basically adds x amount of days depending on the project type and have dependency to the previous key columns ) I wonder how can we have the second sheet updated while honoring formulas so if I change column K, rest of the values are updated based on formula? Likewise should be applicable to the columns downstream such as if I just manually change Column L, Column R and Column U will be changed on sheet 1 then will be updated on sheet2 F, G and H columns.

I.e on the formula on sheet 1, if I enter K as 9/11/2018, formula on column L does the calculation based on the project type, then R is calculated based on L's values ( certain number of days added ) then U is calculated based on R's value ( again another certain amount of days added ).

2) Another thing I realized is that my project full names are actually kept on W (W# = A#&c#) (i.e which also matches to the project names on sheet 2 on Column A starting A:26) . How can we do the name match to Column W instead of Column A when the values on sheet 2 are updated?

 

[mumps]

To change the match to column W, replace this line of code:

Set project = Sheets("Sheet2").Range("A27:A" & Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row).Find(Cells(Target.Row, 1).Value, LookIn:=xlValues, lookat:=xlWhole)

with this line:

Set project = Sheets("Sheet2").Range("A27:A" & Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row).Find(Cells(Target.Row, 23).Value, LookIn:=xlValues, lookat:=xlWhole)

In regards to the "formula" questions, I think it might be easier to follow if you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. It would also be a big help if you could manually create a mock up sheet of what you want the end result to look like. If the workbook contains confidential information, you could replace it with generic data.

 

[hakantoz]

hi mumps, thanks so much again for your help on this. I tried the above code and for some reason it did not work out.

Here is the mock up sheet for your reference with formulas : https://app.box.com/s/tkmvxdbkxkxa8uxr9irqcv9qmj3bd3es

 

[mumps]

The code didn't work because the way you entered "Date 1", "Date 2", "Date 3" and "Date 4" in row 1 of Sheet 1 did not match the way those strings are entered in row 26 of Sheet2. Change the strings in both sheets so that they are like the red strings above so that they match. Then try this macro in the worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("K:K")) Is Nothing Then Exit Sub
    Dim project As Range, rDate As Range
    Set project = Sheets("Sheet2").Range("A27:A" & Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row).Find(Cells(Target.Row, 23).Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not project Is Nothing Then
        Set rDate = Sheets("Sheet2").Rows(26).Find(Cells(1, 11).Value, LookIn:=xlValues, lookat:=xlWhole)
        Sheets("Sheet2").Cells(project.Row, rDate.Column) = Target & Chr(10) & Sheets("Sheet2").Cells(project.Row, rDate.Column)
        Set rDate = Sheets("Sheet2").Rows(26).Find(Cells(1, 12).Value, LookIn:=xlValues, lookat:=xlWhole)
        Sheets("Sheet2").Cells(project.Row, rDate.Column) = Cells(Target.Row, "L") & Chr(10) & Sheets("Sheet2").Cells(project.Row, rDate.Column)
        Set rDate = Sheets("Sheet2").Rows(26).Find(Cells(1, 18).Value, LookIn:=xlValues, lookat:=xlWhole)
        Sheets("Sheet2").Cells(project.Row, rDate.Column) = Cells(Target.Row, "R") & Chr(10) & Sheets("Sheet2").Cells(project.Row, rDate.Column)
        Set rDate = Sheets("Sheet2").Rows(26).Find(Cells(1, 21).Value, LookIn:=xlValues, lookat:=xlWhole)
        Sheets("Sheet2").Cells(project.Row, rDate.Column) = Cells(Target.Row, "U") & Chr(10) & Sheets("Sheet2").Cells(project.Row, rDate.Column)
    End If
End Sub

It should take care of the formula issue. This means that you have to change the date in only column K and not in the other columns.

 

[hakantoz]

Thank you mumps, that code works great once the names are aligned!

As a last question ( I am sorry forgot to ask ) , how can I have the past value to have strikethrough for old numbers that are pushed down? ( tried to do it manually but werent successful with, .Font.Strikethrough = True )

 

[hakantoz]

Also to add to that, one thing I realized, while Row K value is updated, it is definitely updating rest of the cells properly, however, if I try to manually change a value downstream, i.e. date2 and only want date3 and date 4 to be updated, similar for date 3 changes only change date 4 etc...

What it is currently doing is, whatever I enter on date 2, it does the update on sheet2 for date1 as well...