Discussion How to track when values change between columns? in Excel
https://techcommunity.microsoft.com/t5/excel/how-to-track-when-values-change-between-columns/m-p/646336#M31697
<P>Hello Everyone.</P><P> </P><P>If I have a table that looks like this:<BR /><BR /></P><TABLE><TBODY><TR><TD>Requirement</TD><TD>UI Designed</TD><TD>Number of Days</TD><TD>UX Designed</TD><TD>Number of Days</TD><TD>Tested</TD><TD>Number of Days</TD><TD>Released</TD><TD>Number of Days</TD></TR><TR><TD>Release user manual</TD><TD>1</TD><TD> </TD><TD>1</TD><TD> </TD><TD>1</TD><TD> </TD><TD>1</TD><TD> </TD></TR><TR><TD>Software UX</TD><TD>1</TD><TD> </TD><TD>1</TD><TD> </TD><TD>0</TD><TD> </TD><TD>0</TD><TD> </TD></TR><TR><TD>Software UI</TD><TD>1</TD><TD> </TD><TD>0</TD><TD> </TD><TD>0</TD><TD> </TD><TD>0</TD><TD> </TD></TR><TR><TD>Test software</TD><TD>1</TD><TD> </TD><TD>1</TD><TD> </TD><TD>1</TD><TD> </TD><TD>0</TD><TD> </TD></TR></TBODY></TABLE><P> </P><P>Where '0' means not done yet and '1' means task is done.<BR /><BR />How can I track how many days, say 'Software UX' has 1 for UX Designed and then a '1' goes in to 'Tested'? and then the 'Number of Days' next to 'UX Designed' column gets a number..<BR /><BR />This will allow me to get a rough estimate of how many days each requirement remains in each status.<BR /><BR /><SPAN>So, for example:</SPAN><BR /><BR /><SPAN>If 'Software UI' has a 1 in UI Designed on 26th May 2019 (according to system date), and then, on 30th May 2019, I add a 1 in the UX Designed column, then I expect the 'Number of Days' column next to UI Designed to have the value of 4. </SPAN><BR /><BR /><SPAN>Because it is 4 days past since 26th May and 30th May.</SPAN><BR /><BR />I hope that makes sense and thanks for any help.</P>Sun, 26 May 2019 06:04:53 GMTianwuk2019-05-26T06:04:53ZHow to track when values change between columns?
https://techcommunity.microsoft.com/t5/excel/how-to-track-when-values-change-between-columns/m-p/646336#M31697
<P>Hello Everyone.</P><P> </P><P>If I have a table that looks like this:<BR /><BR /></P><TABLE><TBODY><TR><TD>Requirement</TD><TD>UI Designed</TD><TD>Number of Days</TD><TD>UX Designed</TD><TD>Number of Days</TD><TD>Tested</TD><TD>Number of Days</TD><TD>Released</TD><TD>Number of Days</TD></TR><TR><TD>Release user manual</TD><TD>1</TD><TD> </TD><TD>1</TD><TD> </TD><TD>1</TD><TD> </TD><TD>1</TD><TD> </TD></TR><TR><TD>Software UX</TD><TD>1</TD><TD> </TD><TD>1</TD><TD> </TD><TD>0</TD><TD> </TD><TD>0</TD><TD> </TD></TR><TR><TD>Software UI</TD><TD>1</TD><TD> </TD><TD>0</TD><TD> </TD><TD>0</TD><TD> </TD><TD>0</TD><TD> </TD></TR><TR><TD>Test software</TD><TD>1</TD><TD> </TD><TD>1</TD><TD> </TD><TD>1</TD><TD> </TD><TD>0</TD><TD> </TD></TR></TBODY></TABLE><P> </P><P>Where '0' means not done yet and '1' means task is done.<BR /><BR />How can I track how many days, say 'Software UX' has 1 for UX Designed and then a '1' goes in to 'Tested'? and then the 'Number of Days' next to 'UX Designed' column gets a number..<BR /><BR />This will allow me to get a rough estimate of how many days each requirement remains in each status.<BR /><BR /><SPAN>So, for example:</SPAN><BR /><BR /><SPAN>If 'Software UI' has a 1 in UI Designed on 26th May 2019 (according to system date), and then, on 30th May 2019, I add a 1 in the UX Designed column, then I expect the 'Number of Days' column next to UI Designed to have the value of 4. </SPAN><BR /><BR /><SPAN>Because it is 4 days past since 26th May and 30th May.</SPAN><BR /><BR />I hope that makes sense and thanks for any help.</P>Sun, 26 May 2019 06:04:53 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-track-when-values-change-between-columns/m-p/646336#M31697ianwuk2019-05-26T06:04:53ZRe: How to track when values change between columns?
https://techcommunity.microsoft.com/t5/excel/how-to-track-when-values-change-between-columns/m-p/646364#M31698
<P><LI-USER uid="244221"></LI-USER> </P><P>Hi, one idea: Documentate changes automatically with help of Worksheet_change event. </P><P> </P><PRE>Private Sub Worksheet_Change(ByVal Target As Range)<BR />Dim lngRow As Long<BR /><BR />With Sheet2<BR /><BR />lngRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1<BR />.Cells(lngRow , 1).Value = Date<BR />.Cells(lngRow , 2).Value = Time<BR />.Cells(lngRow , 3).Value = Target.Address<BR />.Cells(lngRow , 4).Value = Target.Value<BR />.Cells(lngRow , 5).Value = Environ("username")<BR />.Cells(lngRow , 6).Value = Environ("computername")<BR /><BR />End With<BR /><BR />End Sub</PRE><P> </P><P>Regards from germany</P><P>Bernd</P><P><A href="http://www.vba-Tanker.com" target="_blank" rel="noopener">www.vba-Tanker.com</A></P><P> </P>Sun, 26 May 2019 06:31:23 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-track-when-values-change-between-columns/m-p/646364#M31698Berndvbatanker2019-05-26T06:31:23ZRe: How to track when values change between columns?
https://techcommunity.microsoft.com/t5/excel/how-to-track-when-values-change-between-columns/m-p/646379#M31699
Thanks Bernd for taking the time to reply.<BR /><BR />I'm not really familiar with VBA. I assume I just add the above code to the sheet? What would the end result be running this code?<BR /><BR />Thanks.Sun, 26 May 2019 06:46:52 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-track-when-values-change-between-columns/m-p/646379#M31699ianwuk2019-05-26T06:46:52ZRE: How to track when values change between columns?
https://techcommunity.microsoft.com/t5/excel/how-to-track-when-values-change-between-columns/m-p/646380#M31700
Hi ianwuk, Put the code behind Sheet1. Then change any cells in your sheet1. All changes will be written to sheet2. regards BerndSun, 26 May 2019 06:52:36 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-track-when-values-change-between-columns/m-p/646380#M31700Berndvbatanker2019-05-26T06:52:36ZRe: RE: How to track when values change between columns?
https://techcommunity.microsoft.com/t5/excel/how-to-track-when-values-change-between-columns/m-p/646381#M31701
Thanks very much again, Bernd. I will give it a try!Sun, 26 May 2019 06:55:15 GMThttps://techcommunity.microsoft.com/t5/excel/how-to-track-when-values-change-between-columns/m-p/646381#M31701ianwuk2019-05-26T06:55:15Z