SOLVED
Home

How to track when values change between columns?

%3CLINGO-SUB%20id%3D%22lingo-sub-646336%22%20slang%3D%22en-US%22%3EHow%20to%20track%20when%20values%20change%20between%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-646336%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20have%20a%20table%20that%20looks%20like%20this%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3ERequirement%3C%2FTD%3E%3CTD%3EUI%20Designed%3C%2FTD%3E%3CTD%3ENumber%20of%20Days%3C%2FTD%3E%3CTD%3EUX%20Designed%3C%2FTD%3E%3CTD%3ENumber%20of%20Days%3C%2FTD%3E%3CTD%3ETested%3C%2FTD%3E%3CTD%3ENumber%20of%20Days%3C%2FTD%3E%3CTD%3EReleased%3C%2FTD%3E%3CTD%3ENumber%20of%20Days%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ERelease%20user%20manual%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESoftware%20UX%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ESoftware%20UI%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ETest%20software%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhere%20'0'%20means%20not%20done%20yet%20and%20'1'%20means%20task%20is%20done.%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20can%20I%20track%20how%20many%20days%2C%20say%20'Software%20UX'%20has%201%20for%20UX%20Designed%20and%20then%20a%20'1'%20goes%20in%20to%20'Tested'%3F%26nbsp%3B%20and%20then%20the%20'Number%20of%20Days'%20next%20to%20'UX%20Designed'%20column%20gets%20a%20number..%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20will%20allow%20me%20to%20get%20a%20rough%20estimate%20of%20how%20many%20days%20each%20requirement%20remains%20in%20each%20status.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3ESo%2C%20for%20example%3A%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EIf%20'Software%20UI'%20has%20a%201%20in%20UI%20Designed%20on%2026th%20May%202019%20(according%20to%20system%20date)%2C%20and%20then%2C%20on%2030th%20May%202019%2C%20I%20add%20a%201%20in%20the%20UX%20Designed%20column%2C%20then%20I%20expect%20the%20'Number%20of%20Days'%20column%20next%20to%20UI%20Designed%20to%20have%20the%20value%20of%204.%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EBecause%20it%20is%204%20days%20past%20since%2026th%20May%20and%2030th%20May.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3EI%20hope%20that%20makes%20sense%20and%20thanks%20for%20any%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-646336%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-646364%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20track%20when%20values%20change%20between%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-646364%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F244221%22%20target%3D%22_blank%22%3E%40ianwuk%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20one%20idea%3A%20Documentate%20changes%20automatically%20with%20help%20of%20Worksheet_change%20event.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EDim%20lngRow%20As%20Long%3CBR%20%2F%3E%3CBR%20%2F%3EWith%20Sheet2%3CBR%20%2F%3E%3CBR%20%2F%3ElngRow%20%3D%20.Cells(.Rows.Count%2C%201).End(xlUp).Row%20%2B%201%3CBR%20%2F%3E.Cells(lngRow%20%2C%201).Value%20%3D%20Date%3CBR%20%2F%3E.Cells(lngRow%20%2C%202).Value%20%3D%20Time%3CBR%20%2F%3E.Cells(lngRow%20%2C%203).Value%20%3D%20Target.Address%3CBR%20%2F%3E.Cells(lngRow%20%2C%204).Value%20%3D%20Target.Value%3CBR%20%2F%3E.Cells(lngRow%20%2C%205).Value%20%3D%20Environ(%22username%22)%3CBR%20%2F%3E.Cells(lngRow%20%2C%206).Value%20%3D%20Environ(%22computername%22)%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%20from%20germany%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fwww.vba-Tanker.com%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.vba-Tanker.com%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-646379%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20track%20when%20values%20change%20between%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-646379%22%20slang%3D%22en-US%22%3EThanks%20Bernd%20for%20taking%20the%20time%20to%20reply.%3CBR%20%2F%3E%3CBR%20%2F%3EI'm%20not%20really%20familiar%20with%20VBA.%20I%20assume%20I%20just%20add%20the%20above%20code%20to%20the%20sheet%3F%20What%20would%20the%20end%20result%20be%20running%20this%20code%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-646380%22%20slang%3D%22en-US%22%3ERE%3A%20How%20to%20track%20when%20values%20change%20between%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-646380%22%20slang%3D%22en-US%22%3EHi%20ianwuk%2C%20Put%20the%20code%20behind%20Sheet1.%20Then%20change%20any%20cells%20in%20your%20sheet1.%20All%20changes%20will%20be%20written%20to%20sheet2.%20regards%20Bernd%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-646381%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20How%20to%20track%20when%20values%20change%20between%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-646381%22%20slang%3D%22en-US%22%3EThanks%20very%20much%20again%2C%20Bernd.%20I%20will%20give%20it%20a%20try!%3C%2FLINGO-BODY%3E
ianwuk
Contributor

Hello Everyone.

 

If I have a table that looks like this:

RequirementUI DesignedNumber of DaysUX DesignedNumber of DaysTestedNumber of DaysReleasedNumber of Days
Release user manual1 1 1 1 
Software UX1 1 0 0 
Software UI1 0 0 0 
Test software1 1 1 0 

 

Where '0' means not done yet and '1' means task is done.

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..

This will allow me to get a rough estimate of how many days each requirement remains in each status.

So, for example:

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.  

Because it is 4 days past since 26th May and 30th May.

I hope that makes sense and thanks for any help.

4 Replies

@ianwuk 

Hi, one idea: Documentate changes automatically with help of Worksheet_change event. 

 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngRow As Long

With Sheet2

lngRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(lngRow , 1).Value = Date
.Cells(lngRow , 2).Value = Time
.Cells(lngRow , 3).Value = Target.Address
.Cells(lngRow , 4).Value = Target.Value
.Cells(lngRow , 5).Value = Environ("username")
.Cells(lngRow , 6).Value = Environ("computername")

End With

End Sub

 

Regards from germany

Bernd

www.vba-Tanker.com

 

Thanks Bernd for taking the time to reply.

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?

Thanks.
Solution
Hi ianwuk, Put the code behind Sheet1. Then change any cells in your sheet1. All changes will be written to sheet2. regards Bernd
Thanks very much again, Bernd. I will give it a try!
Related Conversations