MACRO using VBA for contract tracking

[healthcarepropro]

Hello -

If anyone could help, I would be greatly appreciative. I am building a tracker for contracts that will be updated frequently by different users. I want to capture the updates to each contract which are reflected using a VBA macro:

User
Contract No.
Organization
Status of Contract
Status Date

As a new status is entered by a user for each corresponding contract, I want to create a log of the changes. Note: some columns have a drop-down list - I don't if this matter or not.

How can I do that?


Thanks!!!!

[jolivanes]

Re: How can I do that?
By attaching a workbook with a before and after and no personal info.
This gives possible helpers something to go on.

[healthcarepropro]

Thank you! I failed to add the attachment to the original post.

[JeteMc]

Hello healthcarepropro and Welcome to Excel Forum.
For the information in the sample file it would seem that the following formula might work: =INDEX(Sheet1!$A$4:$D$10,ROWS(A$1:A1),MATCH(B$1,Sheet1!$A$3:$D$3,0))
Note that zero values have been hidden using conditional formatting: Cell Value = 0 (font set to white)
I feel that we are going to need a sample with more data on sheets 1 and 2 and with some (manually typed) output on the two sheetlogs in order to be of more help.
Let us know if you have any questions.

[healthcarepropro]

Thanks so much for your feedback. Your method is amazing! But, I need to keep track of who made the edits, and when the edit was made.

I would like for the 'user' column to pull info from the Excel personal info. For example: if Nick, entered any information into sheet2 his name will appear in the user column on sheet2 log followed by the timestamp in "Data Entry', etc.

I have updated the excel sheet with additional info.

[healthcarepropro]

I've attached a new excel sheet to my orignal post.

[JeteMc]

I would like for the 'user' column to pull info from the Excel personal info.

Where on sheet2 is the personal info.?
I feel as if I am missing something.

[healthcarepropro]

Here is a sample of something similar where the user information is pulled I believe from the personal information of the excel program.
excelforum.com/excel-programming-vba-macros/1167509-audit-trail-with-vba.html